<< Parameters for the import procedures | Identity column is GENERATED BY DEFAULT >>

Import into tables with identity columns

<< Parameters for the import procedures | Identity column is GENERATED BY DEFAULT >>

Derby Server and Administration Guide

Specifies the code set of the data in the input file. The code set name should be one
of the Java supported character encoding sets. Data is converted from the specified
code set to the database code set (UTF-8). You can specify a NULL value to interpret
the data file in the same code set as the JVM in which it is being executed. The
CODESET parameter takes an input argument that is a VARCHAR(128) data type.

REPLACE

A non-zero value for the replace parameter will import in REPLACE mode, while
a zero value will import in INSERT mode. REPLACE mode deletes all existing
data from the table by truncating the table and inserts the imported data. The table
definition and the index definitions are not changed. You can import with REPLACE
mode only if the table already exists. INSERT mode adds the imported data to the
table without changing the existing table data. Specifying a NULL value results in an
error. The REPLACE parameter takes an input argument that is a SMALLINT data
type.

If you create a schema, table, or column name as a non-delimited identifier, you must
pass the name to the import procedure using all uppercase characters. If you created a
schema, table, or column name as a delimited identifier, you must pass the name to the
import procedure using the same case that was used when it was created.

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,

, 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

, when you call the procedure. For example: