I have an application that needs to insert about 1-3 thousand rows into a particular table per transaction. Currently the data is moved over 1 row at a time as an insert update or delete. I was wondering if I could make things better by making a single call to insert a bunch of data by union all the data together in a big single insert. This big insert takes twice the time as inserting a bunch of single insert statements between a begin and end. I was wondering if there isn't a best way to do this...
We are executing from C# and we need a kind of bulk collect into sort of thing.
Sadly, the example below that takes 17 seconds was the one I was thinking of. I was not only going to use it for inserting but for merging, where some data might be inserted and other data updated.
My question is, what is the best way to do large blocks of inserts like the second to last or last example from c# where you are effectively bulk collecting into the table.
drop table TEMP;
create table temp nologging as
select 1 a, 1 B, 1 C, 1 D, sysdate E from DUAL;
for rec in (select level L from DUAL connect by level <5000)
insert into TEMP (a,B,C,D,E) values (REC.L,REC.L,REC.L,REC.L, sysdate);
insert into TEMP select L a,L b,L c,L d, sysdate e from (select level L from DUAL connect by level <5000);
--8.16 seconds (most of the insert statements have been omitted for brevity...
Insert into TEMP (A,B,C,D,E) values (1,1,1,1,to_timestamp('09-JUL-12 15.34.05.000000000','DD-MON-RR HH24.MI.SS.FF'));
insert into TEMP (a,B,C,D,E) values (4999,4999,4999,4999,TO_TIMESTAMP('09-JUL-12 15.34.05.000000000','DD-MON-RR HH24.MI.SS.FF'));
--query to build the next insert statement
select 'select ' || L || ' a, ' || L || ' b, ' || L || ' c, ' || L || ' d, sysdate e from dual union all ' a from (select level L from DUAL connect by level <5000);
insert into temp
select 1 a, 1 b, 1 c, 1 d, sysdate e from dual union all
select 4998 a, 4998 b, 4998 c, 4998 d, sysdate e from dual union all
select 4999 a, 4999 b, 4999 c, 4999 d, sysdate e from dual;