Re: Multiple Row Insert in SQL? -- MIDRANGE-L

Every other flavor of DB2 has this capability - someone had to have thought it mattered. Blocking output is certainly one benefit.

The multiple row capability has been available with embedded SQL, using a host variable array (MODS in RPG, e.g) for some time, with some special syntax - I don't think it is unique to iSeries. So the functionality has been in the engine all along, I think, just not surfaced into user syntax.

Why ask why?

;-)

At 09:27 AM 11/4/2003 -0500, you wrote:

What business ap would do it this way though?  Let's see, someone is
entering orders.  I'm not supposed to write out orders until they've:
- entered a certain number of orders
- exit the program
- or I time out the program and exit it for them?
Sure would be heck if someone cancelled the job because of some record
lock, maintenance, etc.

Rob Berendt
--
"They that can give up essential liberty to obtain a little temporary
safety deserve neither liberty nor safety."
Benjamin Franklin


Vern Hamberg <vhamberg@xxxxxxxxxxxxxxxxxxxxxxxxx>
Sent by: midrange-l-bounces@xxxxxxxxxxxx
11/04/2003 08:25 AM
Please respond to
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>


To
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
cc

Subject
Re: Multiple Row Insert in SQL?


Unfortunately that syntax is not available at V5R2 in the iSeries version
of DB2 - it is in all the others. Maybe we can hope. Because a single
statement with multiple sets of values runs better than multiple
statements, each with 1 set of values. This is an optimization
recommendation in the other flavors of DB2.

So, for the moment anyway, out of luck.

Vern

At 05:24 PM 11/3/2003 -0600, you wrote:
>I thought the format was something like
>
>INSERT INTO mytable (field1, field2, field3) values
('blah1','blah2','blah3'),
>('blah2a','blah2b','blah2c'), ('blahagain','andagain','andagain');
>
>HTH,
>Loyd
>
>On Mon, 03 Nov 2003 18:00:16 -0500, "Michael Naughton"
><mnaughton@xxxxxxxxxxxx> wrote:
>
> >Is there any way to insert multiple rows into a table without  using a
> >select clause? I'd like to build a SQL statement in a program that just
> >contains discrete values (no references to anything outside of the
> >statement except the table being inserted into). It works fine with
just
> >one row, but I can't get VALUES to work with more than one.
> >
> >Am I just out of luck?  Thanks!
> >
> >Mike Naughton
> >Senior Programmer/Analyst
> >Judd Wire, Inc.
> >124 Turnpike Road
> >Turners Falls, MA  01376
> >413-863-4357 x444
> >mnaughton@xxxxxxxxxxxx