Importing into tables with identity columns

You can use the SYSCS_UTIL.SYSCS_IMPORT_DATA procedure to import data into a table that contains an identity column. If the identity column is defined as GENERATED ALWAYS, an identity value is generated for a table row whenever the corresponding row field in the input file does not contain a value for the identity column. When a corresponding row field 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 scenarios, the following examples show how to specify arguments in the SYSCS_UTIL.SYSCS_IMPORT_DATA procedure to ignore data for the identity column from the file, and/or omit the column name from the insert column list.

If the REPLACE option 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.

Consider the following table that contains an identity column, c2:

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

• Suppose you want to import data into tab1 from a file myfile.del that does not have identity column information and myfile.del contains three fields with the following data:

  Robert,45.2,J
  Mike,76.9,K
  Leo,23.4,I 
  
  

  To import data from myfile.del into the tab1 table, explicitly list the column names for tab1 without the identity column c2 and execute the SYSCS_UTIL.SYSCS_IMPORT_DATA procedure as follows:

  CALL SYSCS_UTIL.SYSCS_IMPORT_DATA (NULL, 'tab1', 'c1,c3,c4' , null, 
  'myfile.del',null, null,null,0) 
  
  

• Suppose you want import data into tab1 from a file empfile.del that also has identity column information and 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 data from empfile.del into the tab1 table, explicitly specify an insert column list without the identity column c2 and specify the column indexes without identity column data and execute the SYSCS_UTIL.SYSCS_IMPORT_DATA procedure as follows:

  CALL SYSCS_UTIL.SYSCS_IMPORT_DATA (NULL, 'tab1', 'c1,c3,c4' , '1,3,4',
  'empfile.del',null, null,null,0)