LOAD TABLE statement

Description

Imports data into a database table from an external ASCII-format file.

Syntax

LOAD [ INTO ] TABLE [ owner ].table-name
... ( load-specification [, ...] )
... FROM { 'filename-string' | filename-variable } [, ...]
... [ CHECK CONSTRAINTS { ON | OFF } IGNORE CONSTRAINTconstrainttype[, ...] ]
... QUOTES OFF
... ESCAPES OFF
... [ FORMAT { 'ascii' | 'binary' } ]
... [ DELIMITED BY 'string' ]
... [ STRIP { ON | OFF } ]
... [ WITH CHECKPOINT { ON | OFF } ]
... [ { 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 ]
... [ IGNORE CONSTRAINT constrainttype [, ...] ]
... [ MESSAGE LOG ‘string’ ROW LOG ‘string’ [ ONLY LOG logwhat [, ...] ]
... [ LOG DELIMITED BY ‘string’ ]

Parameters

Examples

Example 1

• Loads data from one file into the product table on a Windows system. A tab is used as the column delimiter following the description and color columns.

LOAD TABLE product
( id   ASCII(6),
FILLER(1),
name   ASCII(15),
FILLER(1),
description   '\x09',
size   ASCII(2),
FILLER(1),
color   '\x09',
quantity   PREFIX 2,
unit_price   PREFIX 2,
FILLER(2) )
FROM 'C:\\mydata\\source1.dmp'
QUOTES OFF
ESCAPES OFF
BYTE ORDER LOW
NOTIFY 1000

Example 2

• Loads data from two files into the product_new table (which allows NULL values) on a UNIX system. The tab character is the default column delimiter, and the newline character is the row delimiter.

LOAD TABLE product_new
( id,
name,
description,
size,
color   '\x09'   NULL( 'null', 'none', 'na' ),
quantity   PREFIX 2,
unit_price   PREFIX 2 )
FROM '/s1/mydata/source2.dump', '/s1/mydata/source3.dump'
QUOTES OFF
ESCAPES OFF
BLOCKSIZE 100000
FORMAT ascii
DELIMITED BY '\x09'
ON FILE ERROR CONTINUE
ROW DELIMITED BY '\n'

Usage

The LOAD TABLE statement allows efficient mass insertion into a database table from a file with ASCII or binary data.

The LOAD TABLE options also allow you to control load behavior when integrity constraints are violated and to log information about the violations.

If the WITH CHECKPOINT ON clause is not specified, the file used for loading must be retained in case recovery is required. If WITH CHECKPOINT ON is specified, a checkpoint is carried out after loading, and recovery is guaranteed even if the data file is then removed from the system.

You can use LOAD TABLE on a temporary table, but the temporary table must have been declared with the ON COMMIT PRESERVE ROWS clause or the next COMMIT will remove the rows you've loaded.

You can also specify more than one file to load data. In the FROM clause, you specify each filename-string separated by commas. However, Sybase IQ cannot guarantee that all the data can be loaded because of memory constraints. If memory allocation fails, the entire load transaction is rolled back. The files are read one at a time, and they are 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.

Sybase IQ supports loading from both ASCII and binary data, and it supports both fixed and variable length formats. To handle all of these formats, you must supply a load-specification to tell Sybase IQ what kind of data to expect from each “column” or field in the source file. The column-spec allows you to define these formats:

• To indicate directory paths in Windows systems, the backslash character \ must be represented by two backslashes. Therefore, the statement to load data from the file c:\temp\input.dat into the employee table is:

LOAD TABLE employee
FROM 'c:\\temp\\input.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 names refers to directories on the server machine, not on the client machine.

The following describes each of the clauses of the statement.

Setting CHECK CONSTRAINTS OFF means that IQ ignores all check constraint violations. This can be useful, for example, during database rebuilding. If a table has check constraints that call user-defined functions that are not yet created, the rebuild fails unless this option is set to OFF.

This option is mutually exclusive to the following options. If any of these options are specified in the same load, an error results:

• IGNORE CONSTRAINT ALL

• IGNORE CONSTRAINT CHECK

• LOG ALL

• LOG CHECK

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

To use the newline character as a delimiter, you can specify either the special combination '\n' or its ASCII value '\x0a'. Note that, while you can specify up to four characters in the column-spec delimiter-string, you can only specify a single character in the DELIMITED BY clause

... STRIP OFF ...

Trailing blanks are stripped only for non-quoted strings. Quoted strings retain their trailing blanks. As an alternative, the FILLER option allows you to be more specific in the number of bytes to strip instead of just all the trailing spaces. It is more efficient for Sybase IQ to have this option off, and it adheres to the ANSI standard when dealing with trailing blanks. (char data is always padded, so this option only affects varchar data.)

If WITH CHECKPOINT ON is not specified, and recovery is subsequently required, the data file used to load the table is needed for the recovery to complete successfully. If WITH CHECKPOINT ON is specified, and recovery is subsequently required, it will begin after the checkpoint, and the data file need not be present.

• 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).

• 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.

Only one ON FILE ERROR clause is permitted.

• Delimited with column terminators

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

• ASCII fixed length fields

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 (see above for description) for either this option or FILLER.

• You cannot specify any column-spec (such as ASCII or PREFIX) for a column other than BINARY. This includes the NULL specifications.

• If you need to load null values for a column using the BINARY column-spec, you must specify the WITH NULL BYTE keyword or IQ will return an error.

• You cannot use the DELIMITED BY or ROW DELIMITED BY options with UNLOAD FORMAT.

If CHECK, UNIQUE, NULL, 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.

None.

Standards

Permissions

The permissions required to execute a LOAD TABLE statement depend on the database server -gl command line option, as follows:

• If the -gl option is set to ALL, you must be the owner of the table, have DBA authority, or have ALTER permission.

• If the -gl option is set to DBA, you must have DBA authority.

• If the -gl option is set to NONE, LOAD TABLE is not permitted.