Re: SQL syntax

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: Sun, 05 Mar 2000 14:51:07 GMT
Message-ID: <89ts8r$1qn$1@nnrp1.deja.com>

In article <38C19A19.E5BA620A_at_neadwerx.com>,   "Nickolas E. A. Downey" <nead_at_neadwerx.com> wrote:
> I have a table with two columns of type INT, one column is called u_id
> and the other is called int_id. I want to able to perform a multiple
row
> insert in one query, but I cannot figure out the syntax for the life
of
> me. That is to say sqlplus has shot down everything I have tried.
>
> I would expect that it is something of the following nature...
>
> INSERT INTO mytable ( u_id, int_id ) VALUES (4,1), (4,2);
>
> Any pointers would be appreciated, thanks.
>
> --
> Nickolas Emmanuel A. Downey
> Nead Werx, Inc. [ http://www.neadwerx.com ]
> (678) 296-8677 [ mailto: nead_at_neadwerx.com ]
>

the syntax you are looking for does not exist. insert into t values ... by definition inserts exactly 1 row. insert into t SELECT can insert 0, 1 or more.

You could:

insert into t select 1, 2 from dual union all select 3, 4 from dual;

or

begin

   insert into t values ( 1, 2 );
   insert into t values ( 3, 4 );
end;

--

Thomas Kyte                              tkyte_at_us.oracle.com

Oracle Service Industries
http://osi.oracle.com/~tkyte/index.html --
Opinions are mine and do not necessarily reflect those of Oracle Corp

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sun Mar 05 2000 - 08:51:07 CST