Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: How can I insert multiple values in the same SQL command?
Neil Robinson wrote in message <7f4bhd$upf$1_at_news6.svr.pol.co.uk>...
>I need to insert as many as 10 records in one sql command. I've
tried
>guessing at the correct syntax, but cannot do it. I'll be inserting
data
>from variables rather than from another table, so I can't use the
"INSERT
>([fields]) VALUES (SELECT * FROM....) syntax either.
>
>Any help would be greatly appreciated.
This is a hack, and YMMV. Assuming that your vars are SQLPlus bind variables; if not, then incorporate the statement into a procedure and remove the colons. Also, I'm just whacking this out of the thin blue, so the syntax might be off (you gets what you's pays for... :-( )
INSERT INTO FOO (...)
( SELECT :V1, :V2 FROM dual WHERE :V1 IS NOT NULL
UNION
SELECT :V3, :V4 FROM dual WHERE :V3 IS NOT NULL
UNION
...
}
You are constructing a 10 row dataset by selecting random variables from DUAL; you want to do some IS NOT NULL criteria so that if you run out of values in your variables, you don't end up with a 10 row dataset with a bunch of NULL records because the UNION appended those NULL records. (Actually, without a UNION ALL all you'd get is a single NULL record.)
You could also put your 10 row select from dual union into a subquery and then perform a select * from the subquery and perform the WHERE on this outside query.
...Ru Received on Fri Apr 16 1999 - 12:51:57 CDT