Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Insert multiple rows with a single statement

Re: Insert multiple rows with a single statement

From: <sybrandb_at_hccnet.nl>
Date: Fri, 10 Aug 2007 21:53:22 +0200
Message-ID: <e9gpb3tijhqk4k06h0vsleb88r07vm7fgb@4ax.com>


On Fri, 10 Aug 2007 12:12:02 -0700, Sashi <smalladi_at_gmail.com> wrote:

>All, does PL/SQL support inserting more than one row with a single
>statement, similar to what mySQL has? I looked around but couldn't
>find anything regarding this.
>
>I'm assuming that a single insert to insert, say 10,000 rows would be
>faster than 10,000 insert statements and it seems funny that multiple
>insert is not supported.
>
>Thanks,
>Sashi

Depends what you are up to. If you think Oracle is MySQL sold by another vendor, and you don't recognize you'll need to UNLEARN most of what you learned: don't look any further, and stick to MySQL. If on the other hand you would disclose your version, you might get an adequate answer.
As long as you don't do that, one can only point to generic possibilities, some of them ugly and some of them efficient. Works in all versions:
insert into table foo
select 1, 'bar' from dual
union
select 2, 'bar' from dual;

You get the idea.
Works in all versions: Sqlloader
Works in 8i and higher: BULK INSERT
Works in 9i and higher: external tables.

-- 
Sybrand Bakker
Senior Oracle DBA
Received on Fri Aug 10 2007 - 14:53:22 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US