ASA SQL Reference
SQL Statements
Use this statement to import bulk data into a database table from an external ASCII-format file. Inserts are not recorded in the log file , raising the risk that data will be lost in the event of a crash and making this statement unusable with SQL Remote or with MobiLink remote databases.
LOAD [ INTO ] TABLE [ owner.]table-name [ ( column-name, ... ) ]
FROM filename-string
[ load-option ... ]
load-option :
CHECK CONSTRAINTS { ON | OFF }
| COMPUTES { ON | OFF }
| DEFAULTS { ON | OFF }
| DELIMITED BY string
| ESCAPE CHARACTER character
| ESCAPES { ON | OFF }
| FORMAT { ASCII | BCP }
| HEXADECIMAL {ON | OFF}
| ORDER {ON | OFF}
| PCTFREE percent-free-space
| QUOTES { ON | OFF }
| STRIP { ON | OFF }
| WITH CHECKPOINT { ON | OFF }
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 will be used. If DEFAULTS is off and a non-nullable column is omitted from the column list, the engine 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 will be 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 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 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 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 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'
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.
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 character is a comma. You can specify an alternative column delimiter by providing a string. The same formatting requirements apply as to other SQL strings. In particular, if you wanted to specify tab-delimited values, the hexadecimal ASCII code of the tab character (9) is used. The DELIMITED BY clause is as follows:
...DELIMITED BY '\x09' ...
You can specify delimiters that are up to 255 bytes in length. For example,
...DELIMITED BY '###' ...
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 character. Choosing BCP allows the import of ASE 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 multi-byte character sets.
The HEXADECIMAL option can be used only with the FORMAT ASCII option.
ORDER option 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.
For more information, see Using clustered indexes.
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 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',''''
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.
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. |
For more information, see CONVERSION_ERROR option.
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.
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.
The value percent-free-space is an integer between 0 and 100. The former 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.
| 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. |
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. Before inserting data, you can specify the percentage of each table page that should be left free for later updates. For more information, see the ALTER TABLE statement.
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 temporary tables, but the temporary table must have been created with the ON COMMIT PRESERVE ROWS clause because LOAD TABLE does a COMMIT after the load.
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. LOAD TABLE skips empty lines in the input file.
LOAD TABLE and statistics LOAD TABLE captures column statistics when it loads data in order to create histograms on table columns. If a histogram already exists for a column, LOAD TABLE leaves the existing histogram alone and does not create a new one. If you are loading into an empty table, it is beneficial to drop statistics first.
LOAD TABLE does not generate statistics for columns that contain NULL values for more than 90% of the rows being loaded.
LOAD TABLE saves statistics on base tables for future use. It does not save statistics on global temporary tables.
LOAD TABLE adds statistics only if the number of rows being loaded is greater than the threshold specified in the database option MIN_TABLE_SIZE_FOR_HISTOGRAM (the default is 1000). If the table has at least that many rows, histograms are added as follows:
| Data already in table? | Histogram present? | Action taken |
|---|---|---|
| Yes | Yes | Use existing histograms |
| Yes | No | Don't build histograms |
| No | Yes | Use existing histograms |
| No | No | Build new histograms |
For more information, see Optimizer estimates.
Using dynamically constructed filenames You can execute a LOAD TABLE statement with a dynamically constructed filename by dynamically constructing the entire statement, then executing it using the EXECUTE IMMEDIATE statement. For more information, see the EXECUTE IMMEDIATE statement [SP].
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 ALL, you must be the owner of the table or have DBA authority or have ALTER privilege.
If the -gl option is DBA, you must have DBA authority.
If the -gl option is NONE, LOAD TABLE is not permitted.
For more information, see -gl server option.
Requires an exclusive lock on the table.
Inserts are not recorded in the log file. Thus, the inserted rows may not be recovered in the event of a crash. 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 replicated changes through analysis of the log file.
The LOAD TABLE statement does not fire triggers, including referential integrity actions.
A checkpoint is carried out at the beginning of the operation. A second checkpoint, at the end of the operation, is optional.
Column statistics will be updated if a significant amount of data is loaded.
Automatic commit.
MIN_TABLE_SIZE_FOR_HISTOGRAM option [database]
SQL/92 Vendor extension.
SQL/99 Vendor extension.
Sybase Not applicable.
Following is an example of LOAD TABLE. First, we create a table, 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 filename, via the EXECUTE IMMEDIATE statement:
CREATE PROCEDURE LoadData( IN from_file LONG VARCHAR )
BEGIN
DECLARE cmd LONG VARCHAR;
SET cmd = 'LOAD TABLE DBA.MyTable FROM ' ||
'''d:\\data\\' || from_file || '''';
EXECUTE IMMEDIATE WITH ESCAPES OFF cmd;
END