Results 1 to 14 of 14
  1. #1
    Join Date
    Jan 2004
    Posts
    83

    Unanswered: How do I insert multiple rows with one query

    I have a routine that I need to insert the values into an Oracle database. The trick is that I need these values to be inserted row by row, but within one SQL statement.

    Ultimately, when the code runs, my application produces 12 sets of values:

    Group_ID, Total
    group a, 100
    group b, 23,
    etc.

    Thanks!

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Need some additional info. Are the rows to be inserted in another table(s) or in an external file?
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Jan 2004
    Posts
    83
    The Values will be inserted into one table.

    Table Name : stats_trends
    Table Column: group_id, totals

    My routine produces a total per group_id, so I have 12 unique group_ids with 12 unique totals that I need to archive in my "stats_trends" table.

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Use the following example. You have two tables,

    Table Name : stats_trends
    Table Column: group_id, totals

    Table Name : my_data
    Table Column: group_id, amount

    use the following insert to store the total of "amount" grouped by "group_id"

    insert into stats_trends
    ( select Group_ID, sum(amount)
    from my_data
    group by Group_ID);


    This will insert a stats record for every group into your trend table. One thing that I would change on your stats_trends is to add a date_created column that would allow you to display the trend records by date generated.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Jan 2004
    Posts
    83
    In my case, I do not have 2 tables. The "totals" for each "group_id" are coming from a routine, and are available to me as variables.

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Quote Originally Posted by turk99
    In my case, I do not have 2 tables. The "totals" for each "group_id" are coming from a routine, and are available to me as variables.
    I understand, but what kind of totaling does the routine do that couldn't be done by a select with grouping functions. Could you post your routine? As it stand, if the routine is producing totals, there would be the place to put in an insert to maintain the trend table. If this is a canned package that spits out 12 variables that contain totals then you will have to insert each value into your trend tables.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  7. #7
    Join Date
    Jan 2004
    Posts
    83
    We have a unique situation where they only way to get the information we need is though some data in several data bases, and user input. Thus, when my code does all the calculations, I am left w/ 12 variables w/ 12 totals.

    And because of other restrictions, I am forced to archive these 12 variables with their totals once a month.

    I am trying to avoid using 12 insert queries.

  8. #8
    Join Date
    Apr 2004
    Posts
    246
    put the 12 values into a pl/sql associative array, and do a bulk insert using the FORALL syntax.
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  9. #9
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    That will work (assuming that they are using 9i or above, but what is difficult with doing 12 simple inserts?

    insert into stats_trends values(code1,total1);
    insert into stats_trends values(code2,total2);
    insert into stats_trends values(code3,total3);
    ...
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  10. #10
    Join Date
    Jul 2003
    Posts
    2,296
    loop???

    vCount := total_rows_to_be_inserted;

    for 1..vCount
    loop
    insert into stats_trends values (v1, v2);
    end loop;
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  11. #11
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Quote Originally Posted by turk99
    I am trying to avoid using 12 insert queries.
    In one way or another, you will definitively have to iterate 12 times to fill up or insert your values.. although you can change the way the engine does it.

    Like shoblock said, you can use a varArray or PL/SQL table and use the FORALL method to insert the data into the table desired. But basically, the time it will took you to fill out the 12 values into the varArray/PL/SQL table is the same time (I guess!) it will take the engine to insert the values for you! (as how you described your scenario -- 12 values, 12 inserts). So you must be precise, and you might ask yourself: Will this process get higher as the time comes (say today is 12 value, but tomorrow they can be 24 and so on..) ? Will iterate, fill and then insert is what I want ? Can I not insert these values as I am catching them ?

  12. #12
    Join Date
    Apr 2004
    Posts
    246
    "the time it will took you to fill out the 12 values into the varArray/PL/SQL table is the same time (I guess!) it will take the engine to insert the values for you"

    No.

    The savings are two fold: 1) you perform only one context switch between pl/sql and sql, rather then 12, and 2) oracle only executes the insert only once, cutting down the overhead.

    Keep in mind that everytime your pl/sql loop calls the same stmt, oracle still needs to parse, validate, build a plan.... People claim otherwise, but what if the table was dropped while your code was running - Oracle needs to check for that. What if an index was added or dropped - the plan changes automatically.

    While the savings with 12 inserts won't be great (unless the world's most pathetic network is the real problem), there will be savings, and they increase as the array size increases.
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  13. #13
    Join Date
    Jan 2004
    Posts
    83
    Quote Originally Posted by shoblock
    put the 12 values into a pl/sql associative array, and do a bulk insert using the FORALL syntax.
    can you elaborate on this?

    i'm using a coldfusion interface, and i'm not sure how to use a FORALL within SQL.

    even though we are only talking about 12 queries, and the savings is minimal, i like my code to be condensed whereever it can be...and i'm always looking for ways to decrease the ammount of activity between application and database.

    unforatunately, my solution isn't the best. i have coldfusion create a 2 dimensional array, and i loop over the query, dynamically inserting the values into SQL. still have twelve queries.

    <cfloop index="Counter" from=1 to="12">
    INSERT
    INTO stats_lateGroup (
    group_id,
    total_transactions,
    late_transactions,
    flsa_status,
    begin_dt,
    end_dt)
    VALUES(
    '#MyArray[Counter][1]#',
    #MyArray[Counter][2]#,
    #MyArray[Counter][3]#,
    '#variables.flsa_status#',
    #CreateODBCDate('#variables.begin_dt#')#,
    #CreateODBCDate('#variables.end_dt#')#
    )
    </cfloop>

  14. #14
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    The savings are two fold: 1) you perform only one context switch between pl/sql and sql, rather then 12, and 2) oracle only executes the insert only once, cutting down the overhead.

    Keep in mind that everytime your pl/sql loop calls the same stmt, oracle still needs to parse, validate, build a plan.... People claim otherwise, but what if the table was dropped while your code was running - Oracle needs to check for that. What if an index was added or dropped - the plan changes automatically.

    While the savings with 12 inserts won't be great (unless the world's most pathetic network is the real problem), there will be savings, and they increase as the array size increases.
    You are right shoblock, those are things that will defenitively play a role when we are dealing with such operations. But I was hoping he was using a small table, with a litle amount of data (like he stated) -- which was not the case.

    Just to backup what shoblock said about FORALL, I have made two examples.

    This one is filling up the collection only with 12 elements, using both methods FORALL and FOR LOOP:

    Code:
    SQL> declare
      2   type t_numlist is table of number index by binary_integer;
      3   num_list t_numlist;
      4   first_try number default dbms_utility.get_time;
      5   secon_try number;
      6  begin
      7   for i in 1..12 loop
      8     num_list(i) := i;
      9   end loop;
     10   forall i in 1..num_list.count
     11     insert into stats_trends values (num_list(i),num_list(i)*5);
     12   first_try := round((dbms_utility.get_time-first_try)/2,2);
     13   rollback;
     14   secon_try := dbms_utility.get_time;
     15   for i in 1..12 loop
     16     num_list(i) := i;
     17   end loop;
     18   for i in 1..12 loop
     19     insert into stats_trends values (num_list(i),num_list(i)*5);
     20   end loop;
     21   secon_try := round((dbms_utility.get_time-secon_try)/2,2);
     22   dbms_output.put_line('First  try took: ' || first_try);
     23   dbms_output.put_line('Second try took: ' || secon_try);
     24   rollback;
     25  end;
     26  /
    First  try took: 0
    Second try took: 0
    
    PL/SQL procedure successfully completed.
    
    SQL>
    And this time, I increased the elements top to 1000
    Code:
    SQL> declare
      2   type t_numlist is table of number index by binary_integer;
      3   num_list t_numlist;
      4   first_try number default dbms_utility.get_time;
      5   secon_try number;
      6  begin
      7   for i in 1..1000 loop
      8     num_list(i) := i;
      9   end loop;
     10   forall i in 1..num_list.count
     11     insert into stats_trends values (num_list(i),num_list(i)*5);
     12   first_try := round((dbms_utility.get_time-first_try)/2,2);
     13   rollback;
     14   secon_try := dbms_utility.get_time;
     15   for i in 1..1000 loop
     16     num_list(i) := i;
     17   end loop;
     18   for i in 1..1000 loop
     19     insert into stats_trends values (num_list(i),num_list(i)*5);
     20   end loop;
     21   secon_try := round((dbms_utility.get_time-secon_try)/2,2);
     22   dbms_output.put_line('First  try took: ' || first_try);
     23   dbms_output.put_line('Second try took: ' || secon_try);
     24   rollback;
     25  end;
     26  /
    First  try took: 0
    Second try took: 2
    
    PL/SQL procedure successfully completed.
    
    SQL>

Posting Permissions

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