LOAD TABLE statement

User's Guide

Part VI. SQL Anywhere Reference

Chapter 43. Watcom-SQL Statements
LOAD TABLE statement

Function

To import data into a database table from an external ascii-format file.

Syntax

     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 ]

Usage

Anywhere.

Permissions

Must be the creator of the table or have DBA authority. Requires an exclusive lock on the table.

Side effects

Triggers, including referential integrity actions, are not fired by the LOAD TABLE statement. A COMMIT is performed at the end of the load.

Description

The LOAD TABLE statement allows efficient mass insertion into a database table from an ASCII file. LOAD TABLE is more efficient than the ISQL statement INPUT and can be called from any client application.

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 temporary table, but the temporary table must have been declared with the ON COMMIT PRESERVE ROWS clause, as 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, inserts a zero in numeric columns and an empty string in character columns.

The following list describes each of the clauses of the statement.

Filename-string The filename-string is passed to the engine 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. Therefore, 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 engine, not to the client application. If you are running the statement on a database server on some other 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 (9) 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.

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 engine. 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 that \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 '!'