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))

Suppose that you want to import data into tab1 from a file, myfile.del, that does not have identity column information. The myfile.del file contains three fields with the following data:
```
Robert,45.2,J
Mike,76.9,K
Leo,23.4,I
```
To import the data, you must explicitly list the column names in the tab1 table, except for the identity column c2, when you call the procedure. For example:
```
CALL SYSCS_UTIL.SYSCS_IMPORT_DATA (NULL, 'TAB1', 'C1,C3,C4', 
    null, 'myfile.del',null, null, null, 0)
```
Suppose that you want to import data into tab1 from a file, empfile.del, that also has identity column information. The file contains three fields with the following data:
```
Robert,1,45.2,J
Mike,2,23.4,I
Leo,3,23.4,I
```
To import the data, you must explicitly specify an insert column list without the identity column c2 and specify the column indexes without identity column data when you call the procedure. For example:
```
CALL SYSCS_UTIL.SYSCS_IMPORT_DATA (NULL, 'TAB1', 'C1,C3,C4', 
    '1,3,4', 'empfile.del',null, null, null, 0)
```

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:

You may omit the identity column from the insert column list, in which case Derby will generate a new value for the identity column for each input row. You may use this option whether or not the input file contains values for the identity column, but note that if the input file contains values for the identity column, you must also then omit the identity column from the column indexes when you call the procedure.
You may include the identity column in the insert column list, in which case Derby will use the column values from the input file. Of course, this option is available only if the input file actually contains values for the identity column.

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))

Suppose that you want to import data into tab1 from a file, myfile.del, that does not have identity column information. The myfile.del file contains three fields with the following data:
```
Robert,45.2,J
Mike,76.9,K
Leo,23.4,I
```
To import the data, you must explicitly list the column names in the tab1 table, except for the identity column c2, when you call the procedure. For example:
```
CALL SYSCS_UTIL.SYSCS_IMPORT_DATA (NULL, 'TAB1', 'C1,C3,C4', 
    null, 'myfile.del',null, null, null, 0)
```
Suppose that you want to import data into tab1 from a file, empfile.del, that also has identity column information. The file contains three fields with the following data:
```
Robert,1,45.2,J
Mike,2,23.4,I
Leo,3,23.4,I
```
In this case, suppose that you wish to use the existing identity column values from the input file. To import the data, you may simply pass null for the insert column list and column indexes parameters when you call the procedure. For example:
```
CALL SYSCS_UTIL.SYSCS_IMPORT_DATA (NULL, 'TAB1', NULL, 
    NULL, 'empfile.del',null, null, null, 0)
```
Suppose (again) that you want to import data into tab1 from a file, empfile.del, that also has identity column information, but in this case, suppose that you do not wish to use the identity column values from the input file, but would prefer to allow Derby to generate new identity column values instead. In this case, to import the data, you must specify an insert column list without the identity column c2 and specify the column indexes without identity column data when you call the procedure. For example:
```
CALL SYSCS_UTIL.SYSCS_IMPORT_DATA (NULL, 'TAB1', 'C1,C3,C4', 
    '1,3,4', 'empfile.del',null, null, null, 0)
```