Bulk-Import

Derby provides two import procedures you can use to perform bulk-import operations:

1. To import data from a file to a table, use the SYSCS_UTIL.SYSCS_IMPORT_TABLE procedure. The procedure definition is:

   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.

2. To import data from a file to a subset of columns in a table, use the SYSCS_UTIL.SYSCS_IMPORT_DATA procedure. To import data to subset of columns in a table, you specify insertColumns on the table into which data will be imported and/or specify columnIndexes to import data fields from a file to columns in a table. The procedure definition is:

   SYSCS_UTIL.SYSCS_IMPORT_DATA (IN schemaName VARCHAR(128), 
   IN tableName VARCHAR(128), IN insertColumns VARCHAR(32672),
   IN columnIndexes VARCHAR(32672), 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

  Specifies the schema of the table. You can pass a NULL value to use the default schema name.

• tableName

  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. The string must exactly match case of the table name. Passing a null will result in an error.

• insertColumns

  Specifies the comma separated column names of the table into which the data will be imported. You can pass a NULL value to import into all columns of the table.

• columnIndexes

  Specifies the comma separated column indexes (numbered from one) of the input data fields that will be imported. You can pass a NULL value to use all input data fields in the file.

• fileName

  Specifies the file that contains the data to be imported. If the path is omitted, the current working directory is used. The specified location of the file should refer to the server side location if using the Network Server. Passing a null will result in an error.

• columnDelimiter

  Specifies a column delimiter. The specified character is used in place of a comma to signify the end of a column. You can pass a NULL value to use the default value of a comma.

• characterDelimiter

  Specifies a character delimiter. The specified character is used in place of double quotation marks to enclose a character string. You can pass a NULL value to use the default value of a double quotation mark.

• codeset

  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 pass a NULL value to interpret the data file in the same code set as the JVM in which it is being executed.

• 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 only import with REPLACE mode if the table already exists. INSERT mode adds the imported data to the table without changing the existing table data. Passing a null value will result in an error.