LOAD TABLE statement [UltraLite]

Use this statement to import bulk data into a database table from an external file. This statement also provides support for handling the output of the SQL Anywhere dbunload utility (the reload.sql file). LOAD TABLE is not supported on devices (Windows Mobile, iPhone, embedded Linux).

LOAD [ INTO ] TABLE [ owner.]table-name 
( column-name, ... ) 
FROM stringfilename
[ load-option ... ]

load-option :
CHECK CONSTRAINTS { ON | OFF } 
| COMPUTES { ON | OFF}
| DEFAULTS { ON | OFF }
| DELIMITED BY string
| ENCODING encoding
| ESCAPES { ON }
| FORMAT { ASCII | TEXT}
| ORDER { ON | OFF}| 
| QUOTES { ON | OFF }
| SKIP integer
| STRIP { ON | OFF | BOTH }
| WITH CHECKPOINT { ON | OFF }

comment-prefix : string

encoding : string

Parameters

column-name Use this clause to specify one or more columns to load data into. Any columns not present in the column list become NULL if DEFAULTS is OFF. If DEFAULTS is ON and the column has a default value, that value is used. If DEFAULTS is OFF and a non-nullable column is omitted from the column list, the database server attempts to convert the empty string to the column's type.
When a column list is specified, it lists the columns that are expected to exist in the file and the order in which they are expected to appear. Column names cannot be repeated.

FROM string-filename Use this to specify a file from which to load the data. The string-filename is passed to the database server as a string. The string is therefore subject to the same database 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 Employees table is:

LOAD TABLE Employees
FROM 'c:\\temp\\input.dat' ...

The path name is relative to the database server, not to the client application.
You can use UNC path names to load data from files on computers other than the database server.

load-option clause There are several load options you can specify to control how data is loaded. The following list gives the supported load options:

CHECK CONSTRAINTS clause This clause controls whether constraints are checked during loading. CHECK CONSTRAINTS is ON by default, but the Unload utility (ulunload) writes out LOAD TABLE statements with CHECK CONSTRAINTS set to OFF. Setting CHECK CONSTRAINTS to OFF disables check constraints, which can be useful, for example, during database rebuilding.
COMPUTES clause This option is processed but ignored by UltraLite.
DEFAULTS clause By default, DEFAULTS is set to OFF. If DEFAULTS is OFF, any column not present in the list of columns is assigned NULL. If DEFAULTS is set to OFF and a non-nullable column is omitted from the list, the database server attempts to convert the empty string to the column's type. If DEFAULTS is set to ON and the column has a default value, that value is used.
DELIMITED BY clause Use this clause to specify the column delimiter string. The default column delimiter string is a comma; however, it can be any string up to 255 bytes in length (for example, ... DELIMITED BY '###' ...). The formatting requirements of other SQL strings apply. If you want to specify tab-delimited values, you could specify the hexadecimal escape sequence for the tab character (9), ... DELIMITED BY '\x09' ....
ENCODING clause This clause specifies the character encoding used for the data being loaded into the database.
ESCAPES clause ESCAPES is always ON, therefore characters following the backslash character are recognized and interpreted as special characters by the database server. Newline characters can be included as the combination \n, and 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. A backslash followed by any character other than n, x, X, or \ is interpreted as two separate characters. For example, \q inserts a backslash and the letter q.
FORMAT clause This clause specifies the format of the data source you are loading data from. With TEXT, input lines are assumed to be characters (as defined by the ENCODING option), one row per line, with values separated by the column delimiter string. ASCII is also supported.

QUOTES clause This clause specifies whether strings are enclosed in quotes. UltraLite only supports ON, therefore the LOAD TABLE statement expects strings to be enclosed in quote characters. The quote character is an apostrophe (single quote). The first such character encountered in a string is treated as the quote character for the string. Strings must be terminated by a matching quote.

Column delimiter strings can be included in column values. Also, quote characters are assumed not to be part of the value. Therefore, the following line 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.

'123 High Street, Anytown',(715)398-2354

To include a quote character in a value, 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',''''

SKIP clause Use this clause to specify whether to ignore lines at the beginning of a file. The integer argument specifies the number of lines to skip. You can use this clause to skip over a line containing column headings, for example.
STRIP clause This clause is processed but ignored. This clause specifies whether unquoted values should have leading or trailing blanks stripped off before they are inserted. The STRIP option accepts the following options:
- STRIP ON Strip leading blanks.
- STRIP OFF Do not strip off leading or trailing blanks.
- STRIP BOTH Strip both leading and trailing blanks.
WITH CHECKPOINT clause Use this clause to specify whether to perform a checkpoint. The default setting is OFF. If this clause is set to ON, a checkpoint is issued after successfully completing the statement.

Remarks

LOAD TABLE allows efficient mass insertion into a database table from a file. It is provided primarily as a means of supporting the output of the SQL Anywhere dbunload utility (the reload.sql file).

LOAD TABLE is only supported for Windows and Linux, not Windows Mobile.

With FORMAT TEXT, a NULL value is indicated by specifying no value. For example, if three values are expected and the file contains 1,,'Fred',, then the values inserted are 1, NULL, and Fred. If the file contains 1,2,, then the values 1, 2, and NULL are inserted. Values that consist only of spaces are also considered NULL values. For example, if the file contains 1, ,'Fred',, then values 1, NULL, and Fred are inserted. All other values are considered not NULL. For example, '' (single-quote single-quote) is an empty string. 'NULL' is a string containing four letters.

If a column being loaded by LOAD TABLE does not allow NULL values and the file value is NULL, then numeric columns are given the value 0 (zero), character columns are given an empty string (''). If a column being loaded by LOAD TABLE allows NULL values and the file value is NULL, then the column value is NULL (for all types).

If the table contains columns a, b, and c, and the input data contains a, b, and c, but the LOAD statement only specifies a and b as columns to load data into, the following values are inserted into column c:

if DEFAULTS ON is specified, and column c has a default value, the default value is used.
if column c does not have a default value, and NULLs are allowed, a NULL is used.
if column c has no default value and does not allow NULLs, either a zero (0) or an empty string (''), is used, or an error is returned, depending on the data type of the column.

CREATE TABLE t( a CHAR(100) primary key, let_me_default INT DEFAULT 1, c CHAR(100) );

Following is the content of a file called input.txt:

'this_is_for_column_c', 'this_is_for_column_a', ignore_me

The following LOAD statement loads the file called input.txt:

LOAD TABLE T ( c, a ) FROM 'input.txt' FORMAT TEXT DEFAULTS ON;

The command SELECT * FROM t yields the result set:

a	let_me_default	c
this_is_for_column_a	1	this_is_for_column_c

Discuss this page in DocCommentXchange.