Re: Q: Inserting multiple rows with one INSERT??

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Tue, 07 Sep 1999 12:36:11 -0400
Message-ID: <2jvVNzyUgceJ20ui0CZbZsgxgJM7@4ax.com>

A copy of this was sent to Thilo Frotscher <thilo.frotscher_at_igd.fhg.de> (if that email address didn't require changing) On Tue, 07 Sep 1999 17:51:09 +0200, you wrote:

>Hello everyone,
>the html documentation of Oracle 8i contains a syntax definition for the
>
>INSERT statement. There it is shown, that a single INSERT statement
>can have multiple VALUES-clauses, separated by commas. Thus, it
>should be possible to insert multiple rows into a table using only one
>INSERT statement, shouldn't it??
>
>I tried this, following the syntax definition, but it didn't work:
>
>INSERT INTO test VALUES ('aaa', 'bbb'), VALUES ('ccc', 'ddd');
>
>produces an error message. Why?
>
>Thanks for your help,
>Thilo Frotscher

I filed doc bug 985912 for this.

the wire diagram got messed up from 8.0 to 8.1. The 8.1 wire diagram shows:

     +--------------------+
     |                    | ,

-----+--- values clause --+------> ;

indicating that you could have "insert into t values ( 1 ), values ( 2)"

but they then go onto to show a values clause as being:

values clause = "values ( EXP | SUBQUERY )"

which is wrong as well (it should be exp OPTIONALLY followed by comma and another expression).

In short, they have the repeating arrow in the wrong place, around the values clause instead of the expression IN the values clause which is where it belongs.

You cannot insert >1 row using the syntax above..... You can use something like:

insert into test
select 'aaa', 'bbb' from dual
union all
select 'ccc', 'ddd' from dual;

Or, you can submit as a single statment:

begin

   insert into test values ( 'aaa', 'bbb' );    insert into test values ( 'ccc', 'ddd' ); end;

But even more optimally, you will use bind variables an parse but once the statement:

insert into test values ( :x1, :x2 );

and then execute it 2 times with differing bind values. and if your environment allows it (plsql, OCI, Pro*c for example), you will use array inserts as well.

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Sep 07 1999 - 11:36:11 CDT