SYSCS_UTIL.IMPORT_DATA

The SYSCS_UTIL.IMPORT_DATA system procedure imports data into a subset of columns in a table.

Syntax

You specify the subset of columns either by providing the list of column names an argument to the procedure, or by including the list of column names as the first line of the input file. Columns that are provided as procedure arguments override any columns specified in the input file. If a list of columns is not specified either using arguments or the input file, then an exception is thrown.

Note: If you execute this procedure on a connection that sets the skip-constraint-checks property to true, then GemFire XD uses the PUT INTO DML syntax instead of INSERT to import the data. In addition, GemFire XD ignores all primary key, foreign key, and unique constraints.
Note: 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.
SYSCS_UTIL.IMPORT_DATA (IN schemaName VARCHAR(128),
IN tableName VARCHAR(128), IN insertColumnList 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
An input argument of type VARCHAR(128) that specifies the schema of the table into which the procedure imports data. Pass a NULL value to use the default schema name.
tableName
An input argument of type VARCHAR (128) that specifies the table name of the table into which the procedure imports data. This table cannot be a system table or a declared temporary table. Passing a null will result in an error.
insertColumnList
An input argument of type VARCHAR (32762) that specifies the individual column names (separated by commas) of the table into which the procedure imports data. Pass a NULL value if you instead specified the list of column names as the first line of the input file.
Note: If you pass a NULL value for insertColumnList and the input file does not contain the column list, then procedure execution fails with an exception.
columnIndexes
An input argument of type VARCHAR (32762) that specifies the indexes (numbered from 1 and separated by commas) of the input data fields to import. Passing a NULL value uses all of the input data fields in the file.
fileName
An input argument of type VARCHAR(32672) that specifies the file that contains the data to import. If you do not specify a path, the current working directory is used. Passing a NULL value results in an error.
Note: If you execute this procedure from a thin client connection, the input file must directly accessible by GemFire XD member to which you are connected. If you want to import a local file that is not available to existing GemFire XD members, start a local peer client from which you execute the import procedure.
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 in the input file. 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 in the input file. 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 runs the procedure in REPLACE mode, while a value of zero runs the procedure in INSERT mode. REPLACE mode uses the PUT INTO DML syntax to insert rows without first checking primary key constraints. The table definition and the index definitions are not changed. INSERT mode adds the imported data to the table using the standard INSERT syntax, checking existing primary key values if necessary. Passing a NULL value causes an error.
Note: If you execute this procedure over a connection that sets the skip-constraint-checks property to true, then all inserts use the PUT INTO DML syntax regardless of what value you set for the REPLACE option.

Usage

For additional information and example usage for this procedure see Exporting and Importing Bulk Data from Text Files.

Example

The following example imports some of the data fields from a delimited data file called data.del into the STAFF table:
CALL SYSCS_UTIL.IMPORT_DATA
    (NULL, 'STAFF', null, '1,3,4', 'data.del', null, null, null,0)