multiple insert into oracle database from html for

Results 1 to 2 of 2

Thread: multiple insert into oracle database from html for

  1. #1
    Join Date
    Dec 1969

    Default multiple insert into oracle database from html for

    I have designed a questionnaire in asp and created 3 tables in Oracle. First table is a lookup for questions that has fields such as (Question_code, Question_text), second table is a lookup for answers(Answer_code, answer_txt).<BR>These two tables are pre-populated with all the questions and all the answers that I have on the asp form/questionnaire. <BR>The third table would contain all data filled by the user- requested from the asp form. Fields include (a Primary key NextVal (Autonumber), Foreign key question_code and foreign key answer_code).<BR>I have 32 different question codes - 32 answers codes. Each question and each answer has a code such as 1,2,3...).<BR>On my form,form controls can be checkboxes, radiobuttons, textareas combobox, text ...<BR>My question would be how can I insert data requested from the asp form using a Nextval and inserting all questions and answers with a single insert SQL statement instead of writing 32 insert statements???<BR>Is there a way around this?<BR>

  2. #2
    Join Date
    Dec 1969

    Default RE: multiple insert into oracle database from html

    You can improve the response of your script by passing all the form data to a stored procedure.<BR><BR>In the stored procedure, you will need to do the 32 INSERT statements.<BR><BR>It may be possible to do it using a single INSERT, but it&#039;s up to you to experiment.<BR><BR>In Sql Server, you can specify a SELECT query for the INSERT statement. But I&#039;m uncertain if it works in Oracle, so try it.<BR><BR>INSERT (field1, field2)<BR>SELECT field1, field2<BR>FROM myTable<BR>WHERE fieldId = 10<BR><BR>Extending on the above concept, it may be possible to do a UNION. Since you need to specify 32 SELECT queries, you&#039;ll need to join them with a UNION.<BR><BR>INSERT (field1, field2)<BR>SELECT param1, param2 <BR>UNION SELECT param3, param4<BR>UNION SELECT param5, param6<BR>UNION SELECT param5, param6<BR>...<BR><BR>You can also use SELECT and UNION to put the data into a temporary holding place (table, cursor,...), then you only need to use a single SELECT query (against the temprary holding location) in your INSERT statement.<BR><BR>If you don&#039;t know PL/SQL (Oracle), then it may be a good time to start learning it. If you want to be able to do anything decent with your data, you&#039;ll need to know how to make full use of the various features/objects available through your RDBMS.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts