UNLOAD statement

UNLOAD data-source
{ TO filename
   | INTO FILE filename
   | INTO CLIENT FILE client-filename 
   | INTO VARIABLE variable-name }
[ unload-option ... ]

data-source
[ FROM ] [ TABLE ] [ owner.]table-name 
| [ FROM ] [ MATERIALIZED VIEW ] [ owner.]materialized-view-name
| select-statement

filename : string | variable

client-filename : string | variable

unload-option :
APPEND { ON | OFF }
| BYTE ORDER MARK { ON | OFF }
| { COMPRESSED | NOT COMPRESSED }
| COLUMN DELIMITED BY  string
| DELIMITED BY string
| ENCODING encoding
| { ENCRYPTED KEY 'key' [ ALGORITHM 'algorithm' ] | NOT ENCRYPTED }
| ESCAPE CHARACTER character
| ESCAPES { ON | OFF }
| FORMAT { TEXT | BCP }
| HEXADECIMAL { ON | OFF }
| ORDER { ON | OFF }
| QUOTE string
| QUOTES { ON | OFF }
| ROW DELIMITED BY string

encoding : string

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.
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) should be written. 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 and the ENCODING is UTF-8 or UTF-16, then a BOM is written. 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).
COLUMN DELIMITED BY and DELIMITED BY clauses The string used between columns. The default column delimiter is a comma. You can specify an alternative column delimiter by providing a string up to 255 bytes in length.

ENCODING clause All database data is translated from the database character encoding to the specified CHAR or NCHAR encoding. When ENCODING is not specified, the database's CHAR encoding is used.

If a translation error occurs during the unload operation, it is reported based on the setting of the on_charset_conversion_failure option.

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 MARK 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.
You cannot encrypt the data if you want the data to be appended (APPEND ON).
ESCAPES clause With ESCAPES turned ON (the default), the database server writes escape sequences. Newline characters can be written 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 written as a single backslash. A backslash followed by any character other than n, x, X, or \ is written as two separate characters. For example, \q inserts a backslash and the letter q. It is recommended that the string you specify for the escape character is no longer than one multibyte character.
FORMAT clause Outputs data in either TEXT format or in BCP out format. If you choose TEXT, output lines are written as text characters, one row per line, with values separated by the column delimiter string. If you choose BCP, data including BLOBs are exported as BCP input files for use with Adaptive Server Enterprise. The default format is TEXT.
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.
ESCAPE CHARACTER clause Use this clause to specify the escape character used in the data. The default escape character for characters written 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 '!'
It is recommended that the string you specify for the escape character is no longer than one multibyte character.
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), the quote character, which defaults to a single quote (apostrophe), is 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 '###' .... 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 matches either \r\n or \n.

The UNLOAD select-statement statement allows data from a SELECT statement to be exported to a comma-delimited file. The result set is not ordered unless the SELECT statement contains an ORDER BY clause.

The UNLOAD TABLE statement allows efficient mass exporting from a database table or materialized view into a file. The UNLOAD TABLE statement is more efficient than the Interactive SQL statement OUTPUT, and can be called from any client application.

The database server, or the client application, depending upon whether INTO FILE or INTO CLIENT FILE was specified, respectively, must have operating system permissions to write to the specified file.

When unloading table columns with binary data types, UNLOAD TABLE writes hexadecimal strings, of the form \xnnnn, where n is a hexadecimal digit.

When unloading and reloading a database that has proxy tables, you must create an external login to map the local user to the remote user, even if the user has the same password on both the local and remote databases. If you do not have an external login, the reload may fail because you cannot connect to the remote server.

When unloading into a variable (INTO VARIABLE), the output is converted to a character set as follows:

CHAR write to the variable in CHAR encoding. The ENCODING clause must match the CHAR encoding.
NCHAR write to the variable in NCHAR encoding. The ENCODING clause must match the NCHAR encoding.
BINARY write to the variable in BINARY encoding. The ENCODING clause must match the BINARY encoding; otherwise, the CHAR encoding is used.

If you choose to compress and encrypt the unloaded data, it is compressed first.

UNLOAD TABLE places an exclusive lock on the whole table or materialized view.

During the execution of this statement, you can request progress messages.

You can also use the Progress connection property to determine how much of the statement has been executed.

To retain maximum precision of date values, set the date_format to YYYY-MM-DD.

To retain maximum precision of TIMESTAMP values, set the timestamp_format to YYYY-MM-DD HH:NN:SS.SSSSSS.

To retain maximum precision of TIMESTAMP WITH TIMEZONE values, set the timestamp_with_time_zone_format to YYYY-MM-DD HH:NN:SS.SSSSSS+HH:NN.

When unloading into a variable, no permissions are required (other than the normal permissions required to access the data source).

The permissions required to execute an UNLOAD statement depend on the database server -gl option, as follows:

If the -gl option is set to ALL, you must have SELECT permissions on the table or tables referenced in the UNLOAD statement.
If the -gl option is set to DBA, you must have DBA authority.
If the -gl option is set to NONE, UNLOAD is not permitted.

When writing to a file on a client computer:

WRITECLIENTFILE authority is required.
Write permissions are required for the directory being written to.
The allow_write_client_file database option must be enabled.
The write_client_file secured feature must be enabled.

None. The query is executed at the current isolation level.

SQL/2008 Vendor extension.

The following example unloads the contents of the Products table to a UTF-8-encoded file, productsT.dat:

UNLOAD TABLE Products TO 'productsT.dat' ENCODING 'UTF-8';

The following example creates a variable called @myProducts and then unloads the Products.Name column into the variable:

CREATE VARIABLE @myProducts LONG VARCHAR;
UNLOAD SELECT NAME FROM Products INTO VARIABLE @myProducts;

UNLOAD statement

Syntax

Syntax

Parameters

Remarks

Permissions

Side effects

See also

Standards and compatibility

Example