Re: Doing multiple row inserts using one insert statement

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');

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)