Re: Multiple Row Insert in SQL?

[Vern Hamberg <vhamberg@xxxxxxxxxxxxxxxxxxxxxxxxx>]

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