SYSCS_UTIL.IMPORT_TABLE

Import data from an input file into all of the columns of a table.

The SYSCS_UTIL.IMPORT_TABLE system procedure imports data from an input file into all of the columns of a table. For example, you can use this procedure to import data from a comma-separated value (CSV) file or other delimited file format.

If the table receiving the imported data already contains data, this command appends the new data without affecting existing data. Use TRUNCATE TABLE if you want to clear existing table data before importing data.

Note: See SYSCS_UTIL.IMPORT_TABLE_EX for an updated version of this procedure.

Syntax

SYSCS_UTIL.IMPORT_TABLE (IN SCHEMANAME VARCHAR(128),
IN TABLENAME VARCHAR(128), IN FILENAME VARCHAR(32672),
IN COLUMNDELIMITER CHAR(1), IN CHARACTERDELIMITER CHAR(1),
IN CODESET VARCHAR(128), IN REPLACE SMALLINT)

No result is returned from the procedure.

SCHEMANAME
An input argument of type VARCHAR(128) that specifies the schema of the table. Passing a NULL value will use the default schema name.
TABLENAME
An input argument of type VARCHAR (128) that specifies the table name of the table into which the data is to be imported. This table cannot be a system table or a declared temporary table. Passing a null will result in an error.
FILENAME
An input argument of type VARCHAR(32672) that specifies the file that contains the data to be imported. If you do not specify a path, the current working directory is used. Passing a NULL value will result in an error.
COLUMNDELIMITER
An input argument of type CHAR(1) that specifies a column delimiter. The specified character is used in place of a comma to signal the end of a column. Passing a NULL value will use the default value; the default value is a comma (,).
CHARACTERDELIMITER
An input argument of type CHAR(1) that specifies a character delimiter. The specified character is used in place of double quotation marks to enclose a character string. Passing a NULL value will use the default value; the default value is a double quotation mark (").
CODESET
An input argument of type VARCHAR(128) that specifies the code set of the data in the input file. The name of the code set should be one of the Java-supported character encodings. Data is converted from the specified code set to the database code set (utf-8). Passing a NULL value will interpret the data file in the same code set as the node in which it is being executed.
REPLACE
SQLFire does not implement this argument; specify any value of type SMALLINT. Imported data is always added (inserted) to the target table without changing the existing table data. Use TRUNCATE TABLE if you want to clear existing table data before importing data.
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.

Usage

For additional information on using this procedure see the section Using the bulk import and export procedures in the Apache Derby documentation .

Example

The following example imports data into the STAFF table from a delimited data file called myfile.del with the percentage character (%) as the string delimiter, and a semicolon (;) as the column delimiter:
CALL SYSCS_UTIL.IMPORT_TABLE
    (null, 'STAFF', 'c:/output/myfile.del', ';', '%', null, 0);