INSERT using BIG subquery

From: David Raymond <draymon6_at_csc.com.au>
Date: Thu, 7 Feb 2002 16:13:46 +1100
Message-ID: <a3t2dg$nuo$1_at_gossamer.itmel.bhp.com.au>

Hi,
I have an application which is inserting a 2D array into a table of the form:

ID   NUMBER(7)
ROW  NUMBER(7)
COL  NUMBER(3)

VAL VARCHAR2(100) Rather than do an INSERT for each row I am trying this method:

insert into MY_TABLE(

select 999, 0, 0, 'A' from DUAL
union select 999, 0, 1, 'B' from dual
union select 999, 1, 0, 'C' from dual
union select 999, 1, 1, 'D' from dual

...
);

It seems to work ok and is quite a lot quicker than individual INSERTS. I have tested it on 6500 rows - no problems. Are there any limits to statement sizes, union sizes etc.. that may affect this approach?

Oracle Version 7.3.2.3.0

thanks
regards
David Raymond Received on Thu Feb 07 2002 - 06:13:46 CET