TO clause   The name of the file to unload data into. The filename path is relative to the database server's starting directory. If the file does not exist, it is created. If it already exists, it is overwritten unless APPEND ON is also specified.

INTO FILE clause   Semantically equivalent to TO filename.

INTO CLIENT FILE clause   The file on the client computer into which the data is unloaded. If the file doesn't exist, it is created. If it already exists, it is overwritten unless APPEND ON is also specified. The path is resolved on the client computer relative to the current working directory of the client application.

To unload data onto a client computer using SQL Remote, see PASSTHROUGH statement [SQL Remote].

INTO VARIABLE clause   The variable to unload the data into. The variable must already exist and be of CHAR, NCHAR or BINARY type. The APPEND option causes the unloaded data to be concatenated to the current contents of the variable.

APPEND clause   When APPEND is ON, unloaded data is appended to the end of the file specified. When APPEND is OFF, unloaded data replaces the contents of the file specified. APPEND is OFF by default. This clause cannot be specified when specifying the COMPRESSED or ENCRYPTED clauses, and cannot be used if the file being appended to is compressed or encrypted.

BYTE ORDER MARK clause   Use this clause to specify whether a byte order mark (BOM) is present in the encoding. By default, this option is ON, provided the destination for the unload is a local or client file. When the BYTE ORDER MARK option is ON, UTF-8 and UTF-16 data contains a BOM. If BYTE ORDER MARK is OFF, a BOM is not unloaded.

COMPRESSED clause   Specifies whether to compress the data. The default is NOT COMPRESSED. You cannot compress the data if you want the data to be appended (APPEND ON).

If the file you are appending to is compressed, you must specify the COMPRESSED clause.

DELIMITED BY clause   The string used between columns. The default column delimiter is a comma. You can specify an alternative column delimiter by providing a string. Only the first byte (character) of the string is used as the delimiter.

ENCODING clause   All database data is translated from the database character encoding to the specified character encoding. When ENCODING is not specified, the database's character encoding is used, and translation is not performed.

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

If a translation error occurs during the unload 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 unloads the data using the UTF-8 character encoding:

UNLOAD TABLE mytable TO 'mytable_data_in_utf8.dat' ENCODING 'UTF-8';

Specify the BYTE ORDER clause to include a byte order mark in the data.

ENCRYPTED clause   Specifies whether to encrypt the data. If you specify NOT ENCRYPTED (the default), the data is not encrypted. If you specify ENCRYPTED KEY with a key and no algorithm, the data is encrypted using AES128 and the specified key. If you specify ENCRYPTED KEY with a key and algorithm, the data is encrypted using the specified key and algorithm. The algorithm can be any of the algorithms accepted by the CREATE DATABASE statement. You cannot specify simple encryption. See CREATE DATABASE statement.

You cannot encrypt the data if you want the data to be appended (APPEND ON).

If the file you are appending to is encrypted, you must specify the ENCRYPTED clause.

ESCAPES clause   With ESCAPES turned ON (the default), 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, 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   Outputs data in either TEXT format or in BCP out format. If you choose TEXT, input lines are assumed to be text 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 clause   By default, HEXADECIMAL is ON. Binary column values are written as 0xnnnnnn..., where 0x is a zero followed by an x, and each n is a hexadecimal digit. It is important to use HEXADECIMAL ON when dealing with multibyte character sets.

The HEXADECIMAL clause can be used only with the FORMAT TEXT clause.

ORDER clause   With ORDER ON (the default), the exported data is ordered by clustered index if one exists. If a clustered index does not exist, the exported data is ordered by primary key values. With ORDER OFF, the data is exported in the same order you see when selecting from the table without an ORDER BY clause. Exporting is slower with ORDER ON. However, reloading using the LOAD TABLE statement is quicker because of the simplicity of the indexing step.

For UNLOAD select-statement, the ORDER clause is ignored. However, you can still order the data by specifying an ORDER BY clause in the SELECT statement.

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

QUOTES clause   With QUOTES turned on (the default), single quotes are placed around all exported strings.

ROW DELIMITED BY clause   Use this clause to specify the string that indicates the end of a record. The default delimiter string is a newline (\n). However, it can be any string up to up to 255 bytes in length; for example, ... ROW DELIMITED BY '###' .... The same formatting requirements apply to other SQL strings. If you want 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.

SQL/2003   Vendor extension.