Re: Multiple Row Insert in SQL?

A host structure is a group of host variables used as the source or target for a set of selected values (for example, the set of values for the columns of a row). A host structure array is an array of host structures used in the multiple-row FETCH and blocked INSERT statements.

So what you are saying is that we have this capability with imbedded SQL
via either MODS or DIM'd DS's.  So, application wise, it's not holding you
back.  What you are lamenting is that lack of being able to do it via
interactive SQL?  Then cry me a river.
However, if the problem is that no PC based language has MODS or DIM'd
DS's look-a-likes and, thus, has to use the big long string then you might
have a business case.

If it was the former, then there is so much more I'd rather the DB people
be spending their bucks on.  The latter, that is different, but I suspect
that limitation isn't there.

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 11:52 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?

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
>