Rules for inserting data into an identity column

An identity column contains a unique numeric value for each row in the table. Whether you can insert data into an identity column and how that data gets inserted depends on how the column is defined.

An identity column is a numeric column, defined in a CREATE TABLE or ALTER TABLE statement, that has ascending or descending values. For an identity column to be as useful as possible, its values should also be unique. The column has a SMALLINT, INTEGER, or DECIMAL(p,0) data type and is defined with the AS IDENTITY clause. The AS IDENTITY clause specifies that the column is an identity column. For information about using identity columns to uniquely identify rows, see Identity columns

Before you insert data into an identity column, you must know how the column is defined. Identity columns are defined with the GENERATED ALWAYS or GENERATED BY DEFAULT clause. GENERATED ALWAYS means that Db2 generates a value for the column, and you cannot insert data into that column. If the column is defined as GENERATED BY DEFAULT, you can insert a value, and Db2 provides a default value if you do not supply one.

INSERT INTO T2 (CHARCOL2,IDENTCOL2)
  SELECT * FROM T1;

If IDENTCOL2 is defined as GENERATED ALWAYS, you cannot insert the identity column data from T1 into T2, but you can insert the character column data. To insert only the character data, use one of the following methods:

• Specify only the character column in your INSERT statement, as in the following statement:

  INSERT INTO T2 (CHARCOL2)
    SELECT CHARCOL1 FROM T1;

• Specify the OVERRIDING USER VALUE clause in your INSERT statement to tell Db2 to ignore any values that you supply for system-generated columns, as in the following statement:

  INSERT INTO T2 (CHARCOL2,IDENTCOL2) OVERRIDING USER VALUE
    SELECT * FROM T1;