Reference Manual
CHAPTER 9. SQL Statements
To import data into a database table from an external ASCII-format file.
LOAD [ INTO ] TABLE [ owner ].table-name
... FROM 'filename-string'
... [ FORMAT ASCII ]
... [ DELIMITED BY string ]
... [ STRIP { ON | OFF } ]
... [ QUOTES { ON | OFF } ]
... [ ESCAPES { ON | OFF } ]
... [ ESCAPE CHARACTER character ]
... [ WITH CHECKPOINT { ON | OFF } ]
Must be the owner of the table or have DBA authority.
Requires an exclusive lock on the table.
The table cannot be a declared local temporary table.
Triggers, including referential integrity actions, are not fired by the LOAD TABLE statement. A COMMIT is performed at the end of the load.
The LOAD TABLE statement allows efficient mass insertion into a database table from an ASCII file. LOAD TABLE is more efficient than the Interactive SQL statement INPUT.
Caution
LOAD TABLE is intended solely for fast loading of large amounts of data. It is not intended for routine use in applications.
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.
LOAD TABLE places an exclusive lock on the whole table; it does not fire any triggers associated with the table.
You can use LOAD TABLE on a global temporary table, but the temporary table must have been created with the ON COMMIT PRESERVE ROWS clause, because LOAD TABLE does a COMMIT after the load. LOAD TABLE cannot be used on declared temporary tables.
If the ASCII file has entries such that a column appears to be NULL, LOAD TABLE treats it as null. If the column in that position cannot be NULL, it inserts a zero in numeric columns and an empty string in character columns.
The following list describes each of the clauses of the statement.
FROM filename-string The filename-string is passed to the server as a string. The string is therefore subject to the same formatting requirements as other SQL strings. In particular:
To indicate directory paths, the backslash character \ must be represented by two backslashes. 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 another computer, the directory names refers to directories on the server machine, not on the client machine.
You can use UNC path names to load data from files on computers other than the server. For example, on a Windows for Workgroups, Windows 95, or Windows NT network, you may use the following statement to load data from a file on the client machine:
LOAD TABLE employee FROM '\\\\client\\temp\\input.dat'
FORMAT option The only file format currently supported is ASCII. Input lines are assumed to be ASCII characters, one row per line, with values separated by the column delimiter character.
DELIMITED BY option The default column delimiter character is a comma. You can specify an alternative column delimiter by providing a string. Only the first ASCII character of the string is read. The same formatting requirements apply as to other SQL strings. In particular, to specify tab-delimited values, the hexadecimal ASCII code of the tab character (0) is used. The DELIMITED BY clause is as follows:
...DELIMITED BY '\x09' ...
STRIP option With STRIP turned on (the default), trailing blanks are stripped from values before they are inserted. To turn the STRIP option off, the clause is as follows:
...STRIP OFF ...
Trailing blanks are stripped only for non-quoted strings. Quoted strings retain their trailing blanks. Leading blanks are trimmed, regardless of the STRIP setting, unless they are enclosed in quotes.
QUOTES option With QUOTES turned on (the default), the LOAD statement looks for a quote character. The quote character is either an apostrophe (single quote) or a quotation mark (double quote). The first such character encountered in the input file is treated as the quote character for the input file.
With quotes on, column delimiter characters can be included in column values. Also, quote characters are assumed not to be part of the value. Therefore, a line of the form
'123 High Street, Anytown',(715)398-2354
is treated as two values, not three, despite the presence of the comma in the address. Also, the quotes surrounding the address are not inserted into the database.
To include a quote character in a value, with QUOTES on, you must use two quotes. The following line includes a value in the third column that is a single quote character:
'123 High Street, Anytown','(715)398-2354',''''
ESCAPES option With ESCAPES turned on (the default), characters following the backslash character are recognized and interpreted as special characters by the database server. New line characters can be included as the combination \n, other characters can be included in data as hexadecimal ASCII codes, such as \x09 for the tab character. A sequence of two backslash characters ( \\ ) is interpreted as a single backslash.
ESCAPE CHARACTER option The default escape character for characters stored as hexadecimal codes and symbols is a backslash (\), so \x0A is the linefeed character, for example.
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.
WITH CHECKPOINT option The default setting is OFF. If set to ON, a checkpoint is issued after successfully completing and logging the statement.
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, if will begin after the checkpoint, and the data file need not be present.
SQL/92 Vendor extension.
Sybase Not applicable.