Identity column is GENERATED BY DEFAULT
Derby Server and Administration Guide
73
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
