Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: How can I insert multiple values in the same SQL command?

Re: How can I insert multiple values in the same SQL command?

From: Rudy Zung <_spamkill_zungr_at_prograph-inc.com>
Date: Fri, 16 Apr 1999 13:51:57 -0400
Message-ID: <7f7t8v$ukv$1@mailhost.prograph-inc.com>

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US