Bulk loading data using the LOAD TABLE statement

The LOAD TABLE statement is used for efficient importing of data from a text or binary file into an existing database table. It loads data into any column indexes you have defined, as well as any created automatically.

The permissions needed to execute a LOAD TABLE statement are set on the server command line, using the -gl option. Sybase recommends the -gl all setting, which allows any user to load or unload a table. This is the default setting set by start_asiq. If the -gl option is set to ALL, you must be the owner of the table, have DBA authority, or have ALTER permission, in order to use the LOAD TABLE statement. You also need INSERT permission on the table.

See the description of the ON FILE ERROR load option for what happens when input file errors occur during a load.

To load large amounts of data, most users create command files. To create a command file, follow the instructions in the chapter entitled “Getting Started with DBISQL” in the Introduction to Sybase IQ.

When you issue the LOAD TABLE statement for an IQ table, a savepoint occurs automatically before the data is loaded. If the load completes successfully, Sybase IQ releases the savepoint. If the load fails, the transaction rolls back to the savepoint. This approach gives you flexibility in committing transactions. For example, if you issue two LOAD TABLE commands, you can ensure that either both commit or neither.

When you issue LOAD TABLE for a Catalog Store table, there is no automatic savepoint. If the load succeeds, it commits automatically. If the load fails, it rolls back. You cannot roll back a successful load of a Catalog Store table.

LOAD TABLE allows you to control load behavior when integrity constraints are violated and to selectively log information about the violations. You can specify whether to ignore UNIQUE, NULL, DATA VALUE, and/or FOREIGN KEY constraint violations that occur during a load and the maximum number of violations to ignore before initiating a rollback. You can also direct the load to log information about specific types of integrity constraint violations both per violation in a message log and per row in a row log.

The basic form of the LOAD TABLE statement is:

LOAD TABLE [ owner ].table-name
[ ( load-specification, ... ) ]
FROM 'filename-string', ...
 [ FORMAT { 'ascii' | 'binary' } ]
... [ DELIMITED BY string ]
... [ STRIP { ON | OFF } ]
... [ QUOTES { ON | OFF } ]
... [ ESCAPES { ON | OFF } ]
... [ ESCAPE CHARACTER character ] 
... [ WITH CHECKPOINT ON|OFF ]
... [ load-options ]

The load-specification does the following:

• Lists each column to be loaded and describes the data in it. A column can contain fixed-length data, variable-length characters delimited by a separator, or data that uses a binary prefix to represent the number of bytes being read.

• Specifies FILLER format for any fields you want to skip.

The syntax for load-specification is as follows:

load-specification:	
{ column-name [ column-spec ] |
FILLER ( filler-type ) }

For each column, you can specify a column-spec. If you omit this option, the format information in the load-options applies to this column. The column-spec and load-options format information tell Sybase IQ what type of data to expect, and how to convert it into a compatible data format if necessary.

Syntax for the column-spec is:

column-spec:	
{ ASCII ( input-width ) |
BINARY [ WITH NULL BYTE ] |
PREFIX { 1 | 2 | 4 } |
'delimiter-string' |
DATE ( input-date-format ) |
DATETIME ( input-datetime-format ) }
[ NULL ( { BLANKS | ZEROS | 'literal', ... } ) ]

You can specify the following types of data in the column-spec:

• Data with bytes of fixed length. Although specified by the keyword ASCII, any 8-bit characters may be used, and for 16-bit character sets, two 8-bit characters are used for each 16-bit character. No code conversion is performed for char and varchar fields except truncation, blank stripping, or blank padding. ASCII is also used to fill numeric data, time, and datetime fields. In each case, the conversion is the same if the value were first inserted as a character field, then cast to the data type of the column in the table. The input-width value is an integer value indicating the fixed width in bytes of the input field in every record.

• Binary fields that use a number of PREFIX bytes (1, 2, or 4) to specify the length of the binary input. The BINARY keyword indicates that data is already converted to the internal form (except for when the byte-order load option is specified.

  Note that if the data is unloaded using the extraction facility with the TEMP_EXTRACT_BINARY option set ON, then you must use the BINARY WITH NULL BYTE parameter for each column when you load the binary data.

• Variable-length characters delimited by a separator. You specify the delimiter-string as a string of one to four ASCII characters, or any 8–bit hexadecimal ASCII code that represents a single, non-printing character. The delimiter-string must be enclosed in single quotes. For example, you specify:

  • '\x09' to represent a tab as the terminator.

  • '\x00' for a null terminator (no visible terminator as in “C” strings).

  • '\x0a' for a newline character as the terminator. You can also use the special character combination of \n for newline.

• DATE or DATETIME string as ASCII characters. You must define the input-date-format or input-datetime-format of the string using one of the corresponding formats for the date and datetime data types supported by Sybase IQ. Use DATE for date values and DATETIME for datetime and time values. For information about these data types, see the Sybase IQ Reference Manual.

The NULL portion of the column-spec indicates how to treat certain input values as NULL values when loading into the table column. These characters can include BLANKS, ZEROS, or any other list of literals you define. When you specify a NULL value or read a NULL value from the source file, the destination column must be able to contain NULLs.

ZEROS are interpreted as follows: the cell is set to NULL if (and only if) the input data (before conversion, if ASCII) is all binary zeros (and not character zeros).

• If the input data is character zero, then:

  a) NULL(ZEROS) never causes the cell to be NULL

  b) NULL('0') causes the cell to be NULL

• If the input data is binary zero (all bits clear), then:

  a) NULL(ZEROS) causes the cell to be NULL

  b) NULL('0') never causes the cell to be NULL

For example, if your LOAD statement includes col1 date('yymmdd') null(zeros) and the date is 000000, you will receive an error indicating that 000000 cannot be converted to a DATE(4). To get the load statement to insert a NULL value in col1 when the data is 000000, you must write the NULL clause as null(‘000000’), or modify the data to equal binary zeros and use NULL(ZEROS).

The FILLER clause indicates you want to skip over a specified field in the source input file. For example, there may be characters at the end of rows or even entire fields in the input files that you do not want to add to the table. As with the column-spec definition, FILLER allows you to specify ASCII fixed length of bytes, variable length characters delimited by a separator, and binary fields using PREFIX bytes. The maximum length of a variable-length FILLER column is 512 bytes. FILLER clause syntax is as follows:

FILLER ( filler-type )
filler-type: 
{ input-width | PREFIX { 1 | 2 | 4 } | 'delimiter-string' }

You specify one or more files from which to load data. In the FROM clause, you specify each filename-string, and separate multiple strings by commas.

The files are read one at a time, and processed in a left-to-right order as specified in the FROM clause. Any SKIP or LIMIT value only applies in the beginning of the load, not for each file.

If a load cannot complete, for example due to insufficient memory, the entire load transaction is rolled back.

• If a backslash (\) precedes the characters n, x, or \ it is considered an escape character. For this reason, to indicate directory paths in Windows systems, you must represent the backslash character by two backslashes if the next character is any of those listed. (It is always safe to double the backslashes.) Therefore, the statement to load data from the file c:\newinput.dat into the employee table is:

  LOAD TABLE employee
  FROM 'c:\\newinput.dat' ...
  

• The pathname is relative to the database server, not to the client application. If you are running the statement on a database server on some other computer, the directory name refers to directories on the server machine, not on the client machine. The input file for the load must be on the server machine.

The file specification can be a named pipe. When you load from a named pipe (or FIFO) on Windows, the program writing to the pipe must close the pipe in a special way. The pipe writer must call FlushFileBuffers( ) and then DisconnectNamedPipe( ). (If you do not, Sybase IQ reports an exception from hos_io::Read( ).) This issues a PIPE_NOT_CONNECTED error, which notifies Sybase IQ that the pipe was shut down in an orderly manner rather than an uncontrolled disconnect. See Microsoft documentation for details on these calls.

You can specify several options that describe the format of input data.

...DELIMITED BY '\x09' ...

To use the newline character as a delimiter, you can specify either the special combination '\n' or its ASCII value '\x0a'.

...STRIP OFF ...

Trailing blanks are stripped only for non-quoted strings. Quoted strings retain their trailing blanks. If you don’t require blank sensitivity, you may use the FILLER option allows you to be more specific in the number of bytes to strip instead of just all the trailing spaces.

This option does not apply to ASCII fixed-width inserts. For example, the STRIP option in the following statement is ignored:

LOAD TABLE dba.foo (col1 ascii(3), col2 ascii(3))
FROM foo_data QUOTES OFF ESCAPES OFF STRIP ON

With QUOTES ON, column or row delimiter characters can be included in the column value. Leading and ending quote characters are assumed not to be part of the value and are excluded from the loaded data value.

With QUOTES OFF, Sybase IQ does not strip off apostrophes (single quotes) or quotation marks (double quotes). When it encounters these characters in your input file, it treats them as part of the data. With QUOTES OFF, you cannot include column delimiter characters in column values.

CREATE TABLE t1 (c1 INT, c2 VARCHAR(20), c3 VARCHAR(20))

with the following input data:

1, apple , fruit1	 ,
2, “banana” , “fruit2”,
3, “ pear “, “ fruit3 “,

The result of loading this data is displayed by running the following query:

SELECT c1, c2, c3, LENGTH(c2), LENGTH(c3) FROM t1

The following output displays the result of the query enclosed by ‘<‘ and ‘>’ and the values of the LOAD TABLE options QUOTES and STRIP:

Notes on the results:

• With QUOTES ON and STRIP ON, both leading space and trailing space for the non-enclosed field c2 row 1 are trimmed.

• With QUOTES ON and STRIP OFF, only the leading space for the non-enclosed field c2 row 1 is trimmed.

• With QUOTES OFF and STRIP ON, only the trailing space for the non-enclosed field c2 row 1 is trimmed.

• With QUOTES OFF and STRIP OFF, neither leading space nor trailing space for the non-enclosed field c2 row 1 is trimmed.

• With QUOTES ON and STRIP ON, both leading space and trailing space within quotes for the enclosed fields c2 and c3 row 3 are NOT trimmed.

The following UNIX example specifies a BLOCK FACTOR of 50,000 records along with the PREVIEW option:

LOAD TABLE lineitem
    (l_shipmode ASCII(15),
    l_quantity ASCII(8),
    FILLER(30))
FROM '/d1/MILL1/tt.t'
BLOCK FACTOR 50000 PREVIEW ON

You can specify a wide range of load options. These options tell Sybase IQ how to interpret and process the input file, and what to do when errors occur.

You can specify load options in any order. Syntax for load-options is as follows:

 ...[ { BLOCK FACTOR number | BLOCK SIZE number } ]
... [ BYTE ORDER { NATIVE | HIGH | LOW } ]
... [ LIMIT number-of-rows ]
... [ NOTIFY number-of-rows ]
... [ ON FILE ERROR { ROLLBACK | FINISH | CONTINUE} ]
... [ PREVIEW { ON | OFF } ]
... [ ROW DELIMITED BY 'delimiter-string' ]
... [ SKIP number-of-rows ]
... [ START ROW ID number ]
... [ UNLOAD FORMAT ]
... [ ON PARTIAL INPUT ROW { ROLLBACK | CONTINUE } ]
... [ IGNORE CONSTRAINT constrainttype [, ...] ]
... [ MESSAGE LOG ‘string’ ROW LOG ‘string’
    [ ONLY LOG logwhat [, ...] ] ]
... [ LOG DELIMITED BY ‘string’ ]

The syntax for the parameters is as follows:

constrainttype:
{ CHECK integer | UNIQUE integer
| NULL integer
| FOREIGN KEY integer
| DATA VALUE integer
| ALL integer }

logwhat:
{ CHECK | ALL | NULL | UNIQUE | DATA VALUE | FOREIGN KEY }

Each of these options is described briefly below. For details of all options of the LOAD TABLE statement, see the Sybase IQ Reference Manual.

The default setting for BLOCK FACTOR is 10,000. Higher block factors generally improve the speed of I/O operations. However, consider the following when setting this option:

• If your source is a disk file, memory considerations will determine the best setting for your system.

• If your source is a tape, either use the same blocking factor that was used when creating the tape (for best performance) or a blocking factor that is evenly divisible into it.

• Sybase IQ rejects the insert operation if you specify a BLOCK FACTOR of zero.

• You cannot specify BLOCK FACTOR along with BLOCK SIZE or with any variable-width input fields.

• If you are loading very wide varchar data, reduce the BLOCK FACTOR to 10.

This can be changed using the ESCAPE CHARACTER clause. For example, to use the exclamation mark as the escape character, you would enter:

... ESCAPE CHARACTER '!'

Only one single-byte character can be used as an escape character.

If WITH CHECKPOINT ON is not specified, the file used for loading must be retained in case recovery is required.

The default setting for BLOCK SIZE is 500,000, which is high enough for input from disk files. For tape files, you should specify the same block size that was used when creating the tape. You cannot specify BLOCK SIZE along with BLOCK FACTOR or with any fixed width input fields.

The following UNIX example specifies a BLOCK SIZE of 200,000 bytes:

LOAD TABLE  mm
    (l_orderkey '\x09',
    l_quantity '\x09',
    l_shipdate DATE('YYYY/MM/DD'))
FROM '/d1/MILL1/tt.t'
BLOCK SIZE 200000

• HIGH when multibyte quantities have the high order byte first (for big endian platforms like Sun, IBM AIX, and HP).

• LOW when multibyte quantities have the low order byte first (for little endian platforms like Windows).

Here is a Windows example:

LOAD TABLE nn
    (l_orderkey,
    l_quantity ASCII(PREFIX 2),
    FILLER(2),
FROM 'C:\\iq\archive\\mill.txt'
BYTE ORDER LOW

LIMIT works together with the SKIP option. SKIP indicates where to begin reading from the input file, and LIMIT specifies how many of those rows to insert. SKIP takes precedence over LIMIT. If you specify multiple input files, these options only affect the first file. The following table shows how these options work together:

In the following Windows example, no rows are skipped and up to 1,000,000 rows are inserted.

LOAD TABLE lineitem
    (l_shipmode ASCII(15),
    l_quantity ASCII(8),
    FILLER(30))
FROM 'C:\\iq\archive\\mill.txt'
BLOCK FACTOR 1000
PREVIEW ON
LIMIT 1000000

• ROLLBACK aborts the entire transaction (the default).

• FINISH finishes the insertions already completed and ends the load operation.

• CONTINUE returns an error but only skips the file to continue the load operation. You cannot use this option with partial-width inserts.

This option is especially useful with partial-width inserts. It can help you diagnose failed or skewed insertions due to incompatible data types, or destination column alignment that does not match source columns. Look at the expected column data type and starting position information to determine if you need to use an insert conversion option on a column and/or where and how much filler to use.

• Delimited with column terminators

• Data defined by the DATE or DATETIME column-spec options

• ASCII fixed length fields

The row delimiter can be any string of from 1 to 4 8-bit codes, including any combination of printable characters, and/or any 8–bit hexadecimal code that represents a non-printing character. For example, you specify \x09 to represent a tab as the terminator. For a null terminator (no visible terminator as in “C” strings), you specify \x00.

To use the newline character as a row delimiter, you can specify either the special combination '\n' or its ASCII value '\x0a'.

You cannot use this option if any input fields contain binary data. With this option, a row terminator causes any missing fields to be set to NULL. All rows must have the same row delimiters, and it must be distinct from all column delimiters. The row and field delimiter strings cannot be an initial subset of each other. For example, you cannot specify “*” as a field delimiter and “*#” as the row delimiter, but you could specify “#” as the field delimiter with that row delimiter.

If a row is missing its delimiters, Sybase IQ returns an error and rolls back the entire load transaction. The only exception is the final record of a file where it rolls back that row and returns a warning message.

On Windows, a row delimiter is usually indicated by the newline character followed by the carriage return character. You may need to specify this as the delimiter-string for either this option or FILLER.

The following Windows example sets the column delimiter for the l_orderkey column to tab, and the row delimiter to newline (\x0a) followed by carriage return (\x0d):

LOAD TABLE mm
    (l_orderkey '\x09',
    l_quantity ASCII(4),
    FILLER(6),
    l_shipdate DATE('YYYY/MM/DD'))
FROM 'C:\\iq\\archive\\mill.txt'
ROW DELIMITED BY '\x0a\x0d'

In this UNIX example, Sybase IQ reads 9,000 rows from the input file, skips the first 5,000, and loads the next 4,000. If there are only 8,000 rows in the input file, then only 3,000 rows are loaded.

LOAD TABLE lineitem(
    l_shipmode ASCII(15),
    l_quantity ASCII(8),
FILLER(30))
FROM '/d1/MILL1/tt.t'
BLOCK FACTOR 1000
LIMIT 4000
SKIP 5000
PREVIEW ON

• The format in the column specifications must be BINARY, the default. Specifying ASCII, PREFIX, FILLER, or string-delimiter causes an error.

• You must not use the load options DELIMITED BY and ROW DELIMITED BY.

• To allow NULLs in the data you must specify BINARY WITH NULL BYTE in the column specification. You cannot include NULL in the column-spec in any other way.

• For the sake of consistency with the data being loaded, you can specify BINARY WITH NULL BYTE even when loading into a table column that does not allow NULLs (as specified in CREATE TABLE or ALTER TABLE). However, if you try to load any data into a column that does not allow NULLs, you receive an error.

See the Sybase IQ Installation and Configuration Guide for more information on upgrading.

• CONTINUE issues a warning and continues the load operation. This is the default.

• ROLLBACK aborts the entire load operation and reports the error

  Partial input record skipped at EOF.
  SQLSTATE: QDC32    SQLSTATE: -1000232L
  

If UNIQUE, NULL, CHECK, or FOREIGN KEY is not specified in the IGNORE CONSTRAINT clause, then the load rolls back on the first occurrence of each of these types of integrity constraint violation.

If DATA VALUE is not specified in the IGNORE CONSTRAINT clause, then the load rolls back on the first occurrence of this type of integrity constraint violation, unless the database option CONVERSION_ERROR = OFF. If CONVERSION_ERROR = OFF, then a warning is reported for any DATA VALUE constraint violation and the load continues.

When the load completes, an informational message regarding integrity constraint violations is logged in the .iqmsg file. This message contains the number of integrity constraint violations that occurred during the load and the number of rows that were skipped.

• If the ONLY LOG clause is not specified, then no information on integrity constraint violations is logged. Only the timestamps indicating the start and completion of the load are logged.

• Information is logged on all integrity constraint type violations specified in the ONLY LOG clause.

• If constraint violations are being logged, then every occurrence of an integrity constraint violation generates exactly one row of information in the MESSAGE LOG file.

  The number of rows (errors reported) in the MESSAGE LOG file can exceed the IGNORE CONSTRAINT option limit, because the load is performed by multiple threads running in parallel. More than one thread may report that the number of constraint violations has exceeded the specified limit.

• If constraint violations are being logged, then exactly one row of information is logged in the ROW LOG file for a given row, regardless of the number of integrity constraint violations that occur on that row.

  Note that the number of distinct errors in the MESSAGE LOG file may not exactly match the number of rows in the ROW LOG file. The difference in the number of rows is due to the parallel processing of the load described above for the MESSAGE LOG.

• The MESSAGE LOG and ROW LOG files cannot be raw partitions.

• If the MESSAGE LOG or ROW LOG file already exists, then new information is appended to the file.

• Specifying an invalid filename for the MESSAGE LOG or ROW LOG file generates an error.

• Specifying the same filename for the MESSAGE LOG and ROW LOG files generates an error.

Various combinations of the IGNORE CONSTRAINT and MESSAGE LOG options result in different logging actions, as indicated in the following table:

The following statement loads the data from the file dept.txt into all columns of the department table. This example assumes that no explicit data conversion is needed, and that the width of input columns matches the width of columns in the department table.

LOAD TABLE department
FROM 'dept.txt'