|
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 :
CHECK CONSTRAINTS { ON | OFF }
| COMMENTS INTRODUCED BY comment-prefix
| COMPUTES { ON | OFF }
| DEFAULTS { ON | OFF }
| DELIMITED BY string
| ENCODING encoding
| ESCAPE CHARACTER character
| ESCAPES { ON | OFF }
| FORMAT { ASCII | BCP }
| HEXADECIMAL {ON | OFF }
| ORDER { ON | OFF }
| PCTFREE percent-free-space
| QUOTE string
| QUOTES { ON | OFF }
| ROW DELIMITED BY string
| SKIP integer
| STRIP { ON | OFF | LTRIM | RTRIM | BOTH }
| WITH CHECKPOINT { ON | OFF }
statistics-limitation-options :
STATISTICS { ON [ ALL COLUMNS ]
| OFF
| ON KEY COLUMNS
| 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:
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. If you are running the statement on a database server on another computer, the directory names refer to directories on the database server computer, not on the client computer.
You can use UNC path names to load data from files on computers other than the database server.
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
... ESCAPE CHARACTER '!'
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:
STRIP OFF No stripping of leading or trailing blanks.
STRIP LTRIM Strip leading blanks.
STRIP RTRIM Strip trailing blanks.
STRIP BOTH Strip both leading and trailing blanks
STRIP ON Deprecated. Equivalent to STRIP RTRIM.
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.
| Caution
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.
| Caution
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:
if DEFAULT ON is specified, and the column has a default value, the default value is used.
if the column does not have a DEFAULT value, and NULLs are allowed, a NULL is used.
if the column 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.
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:
LOAD TABLE saves statistics on base tables for future use. It does not save statistics on global temporary tables.
If you are loading into an empty table that may have previously contained data, it may be beneficial to drop statistics for the column before executing the LOAD TABLE statement. See DROP STATISTICS statement.
It is important to note that if column statistics exist when a LOAD TABLE is performed on a column, statistics for the column are not recalculated. Instead, statistics for the new data is inserted into the existing statistics. This means that if the existing column statistics are out-of-date, they will still be out of date after loading new data into the column. If you suspect that the column statistics are out of date, you should consider updating them either before, or after, executing the LOAD TABLE statement. See Updating column statistics.
LOAD TABLE adds statistics only if the table has five or more rows. If the table has at least five rows, histograms are modified as follows:
| Data already in table? | Histogram present? | Action taken |
|---|---|---|
| Yes | Yes | Integrate changes into the existing histograms |
| Yes | No | Do not build histograms |
| No | Yes | Integrate changes into the existing histograms |
| No | No | Build new histograms |
LOAD TABLE does not generate statistics for columns that contain NULL values for more than 90% of the rows being loaded.
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:
If the -gl option is set to ALL, you must be the owner of the table or have DBA authority or have ALTER privileges.
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.
See -gl server option.
Requires an exclusive lock on the table.
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.
SQL/2003 Vendor extension.
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:
CREATE PROCEDURE LoadData( IN from_file LONG VARCHAR )
BEGIN
DECLARE path LONG VARCHAR;
SET path = 'd:\\data\\' || from_file;
LOAD MyTable FROM path;
END;
The following example loads UTF-8-encoded table data into mytable:
LOAD TABLE mytable FROM 'mytable_data_in_utf8.dat' ENCODING 'UTF-8';