Identity column

[dharmadam]

I have a table defined as

CREATE TABLE MYTABLE (
BENEF_ID INT NOT NULL GENERATED ALWAYS AS IDENTITY(START WITH 1
INCREMENT BY 1),
LAST_NAME CHAR(20));

insert into mytable
(select sysibm.identity _val_local(),bn f_last_nm from
claimsa.tbenefi ciary)

This gives the following error.
insert into mytable (select sysibm.identity _val_local(),bn f_last_nm
from claimsa.tbenefi ciary)

DB21034E The command was processed as an SQL statement because it was
not a

valid Command Line Processor command. During SQL processing it
returned:

SQL0798N A value cannot be specified for column "BENEF_ID" which is
defined

as GENERATED ALWAYS. SQLSTATE=428C9

[Serge Rielau]

dharmadam wrote:

CREATE TABLE MYTABLE (
BENEF_ID INT NOT NULL GENERATED ALWAYS AS IDENTITY(START WITH 1
INCREMENT BY 1),
LAST_NAME CHAR(20));

insert into mytable(LAST_NA ME)
(select bnf_last_nm from claimsa.tbenefi ciary)

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Pierre Saint-Jacques]

The message is self-explanatory. You cannot specify a value for an
identity col. which generated always.
Db2 will start with value (1) in your case and increment as you go.

Your statement should then be:
insert into mytable (select bnf_last_nm from claimsa.tbenefi ciary where
????????)
the function identity_val_lo cal applies to retrieve the value of the
just inserted row in a table that has an identity col. defiend.
HTH, Pierre.

dharmadam wrote:

I have a table defined as

CREATE TABLE MYTABLE (
BENEF_ID INT NOT NULL GENERATED ALWAYS AS IDENTITY(START WITH 1
INCREMENT BY 1),
LAST_NAME CHAR(20));

insert into mytable
(select sysibm.identity _val_local(),bn f_last_nm from
claimsa.tbenefi ciary)

This gives the following error.
insert into mytable (select sysibm.identity _val_local(),bn f_last_nm
from claimsa.tbenefi ciary)

DB21034E The command was processed as an SQL statement because it was
not a

valid Command Line Processor command. During SQL processing it
returned:

SQL0798N A value cannot be specified for column "BENEF_ID" which is
defined

as GENERATED ALWAYS. SQLSTATE=428C9

--
Pierre Saint-Jacques - Reply to: sesconsjunk at attglobaljunk dot com
Reconstruct address: Remove the two junk and replace at and dot by
their symbols.
IBM DB2 Cerified Solutions Expert - Administration
SES Consultants Inc.

[dharmadam]

Serge Rielau <sr*****@ca.e ye-be-em.com> wrote in message news:<c8******* ***@hanover.tor olab.ibm.com>.. .

dharmadam wrote:

CREATE TABLE MYTABLE (
BENEF_ID INT NOT NULL GENERATED ALWAYS AS IDENTITY(START WITH 1
INCREMENT BY 1),
LAST_NAME CHAR(20));

insert into mytable(LAST_NA ME)
(select bnf_last_nm from claimsa.tbenefi ciary)

Cheers
Serge

Thanks Serge. You have answer for everything.

[Serge Rielau]

Tarrot Cards, DB2 edition :-)
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab