Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Doing multiple row inserts using one insert statement
Unfortunately, some application servers can't use anonymous PL/SQL blocks.
Not to name names (*cough*ColdFusion), but some companies
(*cough*ColdFusion:Macromedia:Now Adobe) saw fit not to allow their
application servers to execute anonymous PL/SQL blocks.
-- Andreas Oracle Certified DBA and PL/SQL Developer "If you don't eat your meat, you cannot have any pudding. "How can you have any pudding if you don't eat your meat?!?!" --- WARNING: DO NOT REPLY TO THIS EMAIL Reply to me only on this newsgroup "DA Morgan" <damorgan_at_x.washington.edu> wrote in message news:1113864203.76352_at_yasure...Received on Mon Apr 18 2005 - 18:33:18 CDT
> Andreas Sheriff wrote:
>
>> Hi,
>>
>> I was just pondering this, to see if it would work and it did.
>>
>> Here is a snippet of how to do multiple inserts using one insert
>> statement:
>>
>> /* Example of multi-value insert: */
>>
>> create table test
>> (
>> testid number,
>> testval varchar2(4000));
>>
>>
>> insert into test (testid, testval)
>> select 1, 'value of 1' from dual union all
>> select 2, 'value of 2' from dual union all
>> select 3, 'value of 3' from dual;
>>
>>
>> Hypothesizing, it could even be faster if you pin the dual table, but I
>> haven't done any metrics on that, though.
>>
>> Hope this helps to optimize your code.
>
> Reinventing the wheel.
>
> BEGIN
> insert into test (testid, testval) VALUES (1, 'value of 1');
> insert into test (testid, testval) VALUES (2, 'value of 2');
> insert into test (testid, testval) VALUES (3, 'value of 3');
> END;
> /
>
> You might also check out the syntax for INSERT WHEN:
>
> http://www.psoug.org
> click on Morgan's Library
> click on INSERT STATEMENTS
> scroll down to INSERT WHEN
> --
> Daniel A. Morgan
> University of Washington
> damorgan_at_x.washington.edu
> (replace 'x' with 'u' to respond)