Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 10.0.1 » SQL Anywhere Server - SQL Reference » SQL Statements

LOAD STATISTICS statement Next Page

LOAD TABLE statement

Use this statement to import bulk data into a database table from an external file. Inserts are not recorded in the log file, raising the risk that data is lost in the event of a failure and making this statement unusable with SQL Remote or with MobiLink remote databases.


LOAD [ INTO ] TABLE [ owner.]table-name [ ( column-name, ... ) ]
FROM file-name
[ load-option ... ]
[ statistics-limitation-options ]

load-option :
| ENCODING encoding
| ORDER { ON | OFF }
| PCTFREE percent-free-space
| QUOTE string
| SKIP integer

statistics-limitation-options :
| ON ( column-list ) }

file-name : string | variable

comment-prefix : string

encoding : string


Column-name    Any columns not present in the column list become NULL if the DEFAULTS option 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 to appear. Column names cannot be repeated. Column names that do not appear in the list are set to NULL/zero/empty or DEFAULT (depending on column nullability, data type, and the DEFAULT setting). Columns that exist in the input file that are to be ignored by LOAD TABLE can be specified using the column name "filler()".

FROM option    The file-name-string 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:

CHECK CONSTRAINTS option    This option is ON by default, but the Unload utility writes out LOAD TABLE statements with the option set to OFF.

Setting CHECK CONSTRAINTS to OFF disables check constraints. 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.

COMMENTS INTRODUCED BY option    This option allows you to specify the string used in the data file to introduce a comment. When used, LOAD TABLE ignores any line that begins with the string comment-prefix.

In this example, lines in input.dat that start with // are ignored.

LOAD TABLE Employees FROM 'c:\\temp\\input.dat' COMMENTS INTRODUCED BY '//' ...

Comments are only allowed at the beginning of a new line.

If the COMMENTS INTRODUCED BY option is omitted, the data file must not contain comments because they are interpreted as data.

COMPUTES option    By default, COMPUTES is ON. Setting COMPUTES to ON enables recalculation of computed columns.

Setting COMPUTES to OFF disables computed column recalculations. This option is useful, for example, if you are rebuilding a database, and a table has a computed column that calls a user-defined function that is not yet created. The rebuild would fail unless this option was set to OFF.

The Unload utility (dbunload) writes out LOAD TABLE statements with the COMPUTES option set to OFF.

DEFAULTS option    By default, DEFAULTS is OFF. If DEFAULTS is OFF, any column not present in the column list is assigned NULL. 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. If DEFAULTS is ON and the column has a default value, that value is used.

DELIMITED BY option    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 same formatting requirements apply as to other SQL strings. If you want to specify tab-delimited values, you could specify the hexadecimal escape sequence for the tab character (9), ... DELIMITED BY '\x09' ....

ENCODING option    Use the ENCODING option to specify the character encoding used for the data being loaded into the database. All data in the load file must be properly encoded in the specified character encoding. When ENCODING is not specified, the database's character encoding is used, and translation is not performed.

For more information on how to obtain the list of supported SQL Anywhere encodings, see Supported character sets.

If a translation error occurs during the load operation, it is reported based on the setting of the on_charset_conversion_failure option. See on_charset_conversion_failure option [database].

The following example loads the data using UTF-8 character encoding:

LOAD TABLE mytable FROM 'mytable_data_in_utf8.dat' ENCODING 'UTF-8';

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


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

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. 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 option    If you choose ASCII, input lines are assumed to be ASCII characters, one row per line, with values separated by the column delimiter string. Choosing BCP allows the import of Adaptive Server Enterprise-generated BCP out files containing BLOBs.

HEXADECIMAL option    By default, HEXADECIMAL is ON. With HEXADECIMAL ON, binary column values are read as 0xnnnnnn..., where each n is a hexadecimal digit. It is important to use HEXADECIMAL ON when dealing with multibyte character sets.

The HEXADECIMAL option can be used only with the FORMAT ASCII option.

ORDER option    The default for ORDER is ON. If ORDER is ON, and a clustered index has been declared, then LOAD TABLE sorts the input data according to the clustered index and inserts rows in the same order. If the data you are loading is already sorted, you should set ORDER to OFF. See Using clustered indexes.

PCTFREE option    Specifies the percentage of free space you want to reserve for each table page. This setting overrides any permanent setting for the table, but only for the duration of the load, and only for the data being loaded.

The value percent-free-space is an integer between 0 and 100. A value of 0 specifies that no free space is to be left on each page—each page is to be fully packed. A high value causes each row to be inserted into a page by itself.

For more information about PCTFREE, see CREATE TABLE statement.

QUOTE option    The QUOTE clause is for ASCII data only; the string is placed around string values. The default is a single quote (apostrophe).

QUOTES option    With QUOTES turned ON (the default), the LOAD TABLE statement expects strings to be enclosed in quote characters. The quote character is either an apostrophe (single quote) or a quotation mark (double 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.

With QUOTES ON, 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, 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',''''

ROW DELIMITED BY option    Use this clause to specify the string that indicates the end of an input record. The default delimiter string is a newline (\n); however, it can be any string up to 255 bytes in length (for example, ... ROW DELIMITED BY '###' ...). The same formatting requirements apply as to other SQL strings. If you wanted to specify tab-delimited values, you could specify the hexadecimal escape sequence for the tab character (9), ... ROW DELIMITED BY '\x09' .... If your delimiter string contains a \n, it will match either \r\n or \n.

SKIP option    Include a SKIP option to ignore the first few lines of a file. The integer argument specifies the number of lines to skip. You can use this option to skip over a line containing column headings, for example. If the row delimiter is not the default (newline), then skipping may not work correctly if the data contains the row delimiter embedded within a quoted string.

STRIP option    Use the STRIP option to specify whether unquoted values should have leading or trailing blanks stripped off before they are inserted. The STRIP option accepts the following options:

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 the database requires automatic recovery before a CHECKPOINT is issued, the data file used to load the table must be present for the recovery to complete successfully. If WITH CHECKPOINT ON is specified, and recovery is subsequently required, recovery begins after the checkpoint, and the data file need not be present.


If you set the database option conversion_error to Off, you may load bad data into your table without any error being reported. If you do not specify WITH CHECKPOINT ON, and the database needs to be recovered, the recovery may fail as conversion_error is On (the default value) during recovery. It is recommended that you do not load tables with conversion_error set to Off and WITH CHECKPOINT ON not specified.

See conversion_error option [compatibility].

The data files are required, regardless of this option, if the database becomes corrupt and you need to use a backup and apply the current log file.

statistics-limitation-options    Allows you to limit the columns for which statistics are generated during the execution of LOAD TABLE. Otherwise, statistics are generated for all columns. You should only use this option if you are certain that statistics will not be used on some columns. You can specify ON ALL COLUMNS (the default), OFF, ON KEY COLUMNS, or a list of columns for which statistics should be generated.


LOAD TABLE is intended solely for fast loading of large amounts of data. LOAD TABLE does not write individual rows to the transaction log.

LOAD TABLE allows efficient mass insertion into a database table from a file. LOAD TABLE is more efficient than the Interactive SQL statement INPUT.

LOAD TABLE places a write lock on the whole table. For base tables and global temporary tables a commit is performed. For local temporary tables, a commit is not performed

When loading data from a UTF-16 or UTF-8 data file, LOAD TABLE ignores the byte order mark (BOM) if it is present. The database server assumes that the data has the same byte order as that of the computer on which the database server is running.

Do not use the LOAD TABLE statement on a global temporary table for which ON COMMIT DELETE ROWS was specified, either explicitly or by default, at creation time. However, you can use LOAD TABLE if ON COMMIT PRESERVE ROWS or NOT TRANSACTIONAL was specified.

With FORMAT ASCII, 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 LOAD TABLE statement contains a column list, a column not specified in the column list is loaded as follows:

LOAD TABLE and column statistics    In order to create histograms on table columns, LOAD TABLE captures column statistics when it loads data. The histograms are used by the optimizer For more information on how column statistics are used by the optimizer, see Optimizer estimates and column statistics.

Following are additional tips about loading and column statistics:

Using dynamically constructed file names    You can execute a LOAD TABLE statement with a dynamically constructed file name by assigning the file name to a variable and using the variable name in the LOAD TABLE statement.


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

See -gl server option.

Requires an exclusive lock on the table.

Side effects

Automatic commit except for local temporary tables

Inserts are not recorded in the log file. Thus, the inserted rows may not be recovered in the event of a failure. In addition, the LOAD TABLE statement should never be used in a database involved in SQL Remote replication or databases used as MobiLink clients because these technologies replicate changes through analysis of the log file.

The LOAD TABLE statement does not fire any triggers associated with the table.

A checkpoint is carried out at the beginning of the operation. A second checkpoint, at the end of the operation, is optional.

Column statistics are updated if a significant amount of data is loaded.

See also
Standards and compatibility

Following is an example of LOAD TABLE. First, you create a table, and then load data into it using a file called input.txt.

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

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

ignore_me, this_is_for_column_c, this_is_for_column_a

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

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

The command SELECT * FROM t yields the result set:

this_is_for_column_a, 1, this_is_for_column_c

Execute the LOAD TABLE statement with a dynamically-constructed file name, via the EXECUTE IMMEDIATE statement:

    SET path = 'd:\\data\\' || from_file;
    LOAD MyTable FROM path;

The following example loads UTF-8-encoded table data into mytable:

LOAD TABLE mytable FROM 'mytable_data_in_utf8.dat' ENCODING 'UTF-8';