Re: Insert multiple rows with a single statement

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 Received on Fri Aug 10 2007 - 15:24:22 CDT