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

Hi.

 Is it possible to use BULK BINDs of PL/SQL in your case?  ( It's like array processing used in PRO*C )?

 Michael.

In article <2jvVNzyUgceJ20ui0CZbZsgxgJM7_at_4ax.com>,   tkyte_at_us.oracle.com wrote:
> 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
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Tue Sep 07 1999 - 16:21:13 CDT