DB2 UDB V7 (Inserting into Identity Columns)

This tip provided by Experts Exchange.

Question:

I am working with DB2 UDB v7 and need to insert into a tablewith an identity column.

Example:

SourceTable:
Student    CourseGrade
——-    ———–
Fred       A
Wilma      B

TargetTable Desired result:  
(SurrogateKey is defined as
Int Generated Always As Identity
(Start with 1, Increment by 1, No Cache)
SurrogateKey     Student    CourseGrade
————     ——-    ———–
1               Fred      A
2               Wilma     B

I want to write a statement like:
Insert into TargetTable
Select
  Student,
  CourseGrade
From SourceTable

This produces an error “The number of values assigned is not the same asthe number of specified or
implied columns”

Is there a way to specify that the first column is inserted into an identitycolumn?

Accepted Answer:

To create records using the identity column you shouldspecify only the fields that need populating.

insert into targettable (student,coursegrade) select student,coursegrade fromsourcetable;

or

insert into targettable (student,coursegrade) select * from sourcetable;

If updating with values use

insert into targettable (student,coursegrade) values (‘Fred’,’A’);

Written on 2/06/2001