Re: SQL to increment 'identity' column for multiple rows in one insert statement?

[Luis Rodriguez <luisro58@xxxxxxxxx>]

Luke,

Check out SQL  IDENTITY columns, as in:

ALTER TABLE MYLIB/MYTABLE

   ADD COLUMN SEQNUMBER NUMERIC (10 , 0)

       AS IDENTITY (START WITH 1 INCREMENT BY 1  )



This would create an column that increments automatically every time you
insert a row. Please be aware that there is a CACHE clause that determines
if the some sequence numbers are "preallocated" (kept in memory) or not
(default is 20). If your application fails, for instance, it is probable
that some sequence numbers get lost. There also a NO CACHE option.

Regards,


Luis Rodriguez
IBM Certified Systems Expert — eServer i5 iSeries
--



On Wed, Jan 12, 2011 at 10:07 AM, Luke Gerhardt <lgerhardt@xxxxxxxxx> wrote:

> I have a column in a particular file that I need to keep unique.  I also
> need to add a batch of records to that file.  In testing, I currently
> read into a host variable the current max(index)+1 to get the next
> available one and then use that in my subsequent insert statement.
> Naturally, when I do I get the same index # for each record.  How can I
> do one insert statement and guarantee a different index for each?
>
> I thought of doing a (select max(index)+1) as part of the insert
> statement itself, but was concerned that it would only evaluate that
> once and not for each record.  There is no commitment control in use.
>
> Here's my code:
> ...
>   exec sql
>     select max(vfindex) + 1,
>       into :index
>       from mylib/mylf;
> ...
>   exec sql
>     insert into mylib/mypf(vfindex,
>                            vfpnbr,
>                            vfmodel,
>                            vfgroupset)
>       select :index,
>              subpart,
>              modl,
>              :grouppart
>         from mylib/mylf join mylf2 on subpart = part
>        where part = :grouppart and atyp='F_LVL';
> ...
>
> Thanks!
>
>
> --
> This is the RPG programming on the IBM i / System i (RPG400-L) mailing list
> To post a message email: RPG400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l.