OraFAQ Forum: SQL & PL/SQL » INSERT using BIG subquery

Home » SQL & PL/SQL » SQL & PL/SQL » INSERT using BIG subquery

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

INSERT using BIG subquery [message #18592]

Wed, 06 February 2002 19:11

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

Report message to a moderator

Re: INSERT using BIG subquery [message #18596 is a reply to message #18592]

Thu, 07 February 2002 03:42

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.

Report message to a moderator

Previous Topic:	PL-SQL set object or selection from table variable
Next Topic:	what is the difference between '' and ' '

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat May 17 06:27:37 CDT 2025