Re: Doing multiple row inserts using one insert statement

From: Rene Nyffenegger <rene.nyffenegger_at_gmx.ch>
Date: Mon, 18 Apr 2005 23:32:07 +0000 (UTC)
Message-ID: <d41g1n$r0t$1@klatschtante.init7.net>

On 2005-04-18, Andreas Sheriff <spamcontrol_at_iion.com> 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.

If you have 10g and like it a bit more esoteric:

create table test (
  testid number,
  testval varchar2(20)
);

insert into test
  select i, 'Value of ' || i from (select 1 i from dual)   model

    dimension by (0 d)
    measures     (i)
    rules iterate(2) (
      i[iteration_number+1] = i[iteration_number]+1

    );

select * from test;

drop table test;

-- 
  Rene Nyffenegger
  http://www.adp-gmbh.ch/

Received on Mon Apr 18 2005 - 18:32:07 CDT