Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!drn.maxwell.syr.edu!news.maxwell.syr.edu!news-hog.berkeley.edu!newsfeed.berkeley.edu!ucberkeley!sn-xit-03!sn-xit-11!sn-xit-08!sn-post-01!supernews.com!corp.supernews.com!not-for-mail
From: DA Morgan <damorgan@x.washington.edu>
Newsgroups: comp.databases.oracle.server
Subject: Re: Doing multiple row inserts using one insert statement
Date: Mon, 18 Apr 2005 15:47:05 -0700
Organization: Ye 'Ol Disorganized NNTPCache groupie
Message-ID: <1113864203.76352@yasure>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.7.2) Gecko/20040804 Netscape/7.2 (ax)
X-Accept-Language: en-us, en
MIME-Version: 1.0
References: <eRU8e.35493$A31.18989@fed1read03>
In-Reply-To: <eRU8e.35493$A31.18989@fed1read03>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
Cache-Post-Path: yasure!unknown@oracle.advtechserv.com
X-Cache: nntpcache 2.4.0b5 (see http://www.nntpcache.org/)
X-Complaints-To: abuse@supernews.com
Lines: 47
Xref: dp-news.maxwell.syr.edu comp.databases.oracle.server:240716

Andreas Sheriff wrote:

> Hi,
> 
> I was just pondering this, to see if it would work and it did.
> 
> Here is a snippet of how to do multiple inserts using one insert statement:
> 
> /* Example of multi-value insert: */
> 
> create table test
>  (
>   testid number,
>   testval varchar2(4000));
> 
> 
> insert into test (testid, testval)
> select 1, 'value of 1' from dual union all
> select 2, 'value of 2' from dual union all
> select 3, 'value of 3' from dual;
> 
> 
> Hypothesizing, it could even be faster if you pin the dual table, but I 
> haven't done any metrics on that, though.
> 
> Hope this helps to optimize your code.

Reinventing the wheel.

BEGIN
   insert into test (testid, testval) VALUES (1, 'value of 1');
   insert into test (testid, testval) VALUES (2, 'value of 2');
   insert into test (testid, testval) VALUES (3, 'value of 3');
END;
/

You might also check out the syntax for INSERT WHEN:

http://www.psoug.org
click on Morgan's Library
click on INSERT STATEMENTS
scroll down to INSERT WHEN
-- 
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)