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: William Robertson <williamr2019_at_googlemail.com>
Date: Sun, 12 Aug 2007 02:26:57 -0700
Message-ID: <1186910817.274812.178370@22g2000hsm.googlegroups.com>


On Aug 10, 9:24 pm, Sashi <small..._at_gmail.com> wrote:
> On Aug 10, 3:53 pm, sybra..._at_hccnet.nl wrote:
>
>
>
> > On Fri, 10 Aug 2007 12:12:02 -0700, Sashi <small..._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
>
> Ok, here's an example (I'm using 10g).
> insert into onsite_support_costs (SITE_ID, LB_CODE,
> ONSITE_SUPPORT_COST) values ('Site12284','HZ10',1944.96);
> insert into onsite_support_costs (SITE_ID, LB_CODE,
> ONSITE_SUPPORT_COST) values ('Site14041','JH96',1728.85);
> insert into onsite_support_costs (SITE_ID, LB_CODE,
> ONSITE_SUPPORT_COST) values ('Site14942','JV98',28208.94);
> insert into onsite_support_costs (SITE_ID, LB_CODE,
> ONSITE_SUPPORT_COST) values ('Site00574','4014',432.21);
> insert into onsite_support_costs (SITE_ID, LB_CODE,
> ONSITE_SUPPORT_COST) values ('Site00570','4009',8288.03);
> insert into onsite_support_costs (SITE_ID, LB_CODE,
> ONSITE_SUPPORT_COST) values ('Site01445','7127',1080.53);
> insert into onsite_support_costs (SITE_ID, LB_CODE,
> ONSITE_SUPPORT_COST) values ('Site01490','7199',8288.03);
>
> I have 200 such rows. Instead of running the insert command 200 times,
> is there a way of running it once and pass to it all data values?
> Thanks,
> Sashi

The equivalent syntax in Oracle is

INSERT ALL
INTO onsite_support_costs (site_id, lb_codeE, onsite_support_cost) VALUES ('Site01490','7199',8288.03)
INTO onsite_support_costs (site_id, lb_codeE, onsite_support_cost) VALUES ('Site01491','7200',8288.04)
INTO onsite_support_costs (site_id, lb_codeE, onsite_support_cost) VALUES ('Site01492','7201',8288.05)
SELECT * FROM dual;

Whether or not this is an efficient approach in your scenario is another question. Received on Sun Aug 12 2007 - 04:26:57 CDT

Original text of this message

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