Home » SQL & PL/SQL » SQL & PL/SQL » INSERT using BIG subquery
INSERT using BIG subquery [message #18592] Wed, 06 February 2002 19:11 Go to next message
David Raymond
Messages: 1
Registered: February 2002
Junior Member
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
Re: INSERT using BIG subquery [message #18596 is a reply to message #18592] Thu, 07 February 2002 03:42 Go to previous message
Jon
Messages: 483
Registered: May 2001
Senior Member
Statement size is 65K. I'm not sure if unions count as "nested queries" (255 limit); I wouldn't think so, but you never know.
Previous Topic: PL-SQL set object or selection from table variable
Next Topic: what is the difference between '' and ' '
Goto Forum:
  


Current Time: Fri Sep 17 10:40:57 CDT 2021