The SYSCS_UTIL.SYSCS_IMPORT_TABLE system procedure
imports data from an input file into all of the columns of a table. If the
table receiving the imported data already contains data, you can either replace
or append to the existing data.
Derby issues a COMMIT or a
ROLLBACK statement after each import and export procedure is run (a COMMIT if
the procedure completes successfully, a ROLLBACK if it fails). For this reason,
you should issue either a COMMIT or ROLLBACK statement to complete all
transactions and release all table-level locks before you invoke an import or
export procedure.
For more information on using this procedure, see the section
"Importing and exporting data" in the
Derby Server and Administration Guide.
Syntax
SYSCS_UTIL.SYSCS_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 JVM in which it is being executed.
- REPLACE
- A input argument of type SMALLINT. A non-zero value will run in REPLACE
mode, while a value of zero will run in INSERT mode. REPLACE mode deletes
all existing data from the table by truncating the data object, and inserts
the imported data. The table definition and the index definitions are not
changed. INSERT mode adds the imported data to the table without changing
the existing table data. Passing a NULL will result in an error.
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.
Execute privileges
If authentication and SQL authorization are both enabled, only the
database owner has
execute privileges on this procedure by default. See "Configuring user
authentication" and "Configuring user authorization" in the
Derby Security Guide for more information. The
database owner can grant access to other users. The user must also have INSERT
privileges on the table.
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.SYSCS_IMPORT_TABLE
(null, 'STAFF', 'c:/output/myfile.del', ';', '%', null,0);