Import into tables that contain identity columns

You can use the either the SYSCS_UTIL.SYSCS_IMPORT_DATA procedure or the SYSCS_UTIL.SYSCS_IMPORT_DATA_LOBS_FROM_EXTFILE procedure to import data into a table that contains an identity column. The approach that you take depends on whether the identity column is GENERATED ALWAYS or GENERATED BY DEFAULT.

Identity columns and the REPLACE parameter

If the REPLACE parameter is used during import, Derby resets its internal counter of the last identity value for a column to the initial value defined for the identity column.

Identity column is GENERATED ALWAYS

If the identity column is defined as GENERATED ALWAYS, an identity value is always generated for a table row. When a corresponding row in the input file already contains a value for the identity column, the row cannot be inserted into the table and the import operation will fail.

To prevent such failure, the following examples show how to specify parameters in the SYSCS_UTIL.SYSCS_IMPORT_DATA and SYSCS_UTIL.SYSCS_IMPORT_DATA_LOBS_FROM_EXTFILE procedures to ignore data for the identity column from the file, and omit the column name from the insert column list.

The following table definition contains an identity column, c2, and is used in the examples below:

CREATE TABLE tab1 (c1 CHAR(30), c2 INT GENERATED ALWAYS AS IDENTITY, 
   c3 REAL, c4 CHAR(1))

Identity column is GENERATED BY DEFAULT

If the identity column is defined as GENERATED BY DEFAULT, an identity value is generated for a table row only if no explicit value is given. This means that you have several options, depending on the contents of your input file and the desired outcome of the import processing:

The following table definition contains an identity column, c2, and is used in the examples below:

CREATE TABLE tab1 (c1 CHAR(30),
   c2 INT GENERATED BY DEFAULT AS IDENTITY, 
   c3 REAL, c4 CHAR(1))