UNLOAD statement

Unloads data from a data source into a file.

Syntax

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 | ALL }
| ROW DELIMITED BY string
| WITH COLUMN NAMES

encoding : string

algorithm : 
'AES' | 'AES256' | 'AES_FIPS' | 'AES256_FIPS'

Parameters

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. 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.

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. The key can be either a string or a variable name. 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 the SIMPLE obfuscation algorithm.

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 line feed character, for example. This can be changed using the ESCAPE CHARACTER clause.

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 apostrophe ('). When using QUOTES ALL, the quote character can only be a single apostrophe (') or quotation mark (").
QUOTES clause
With QUOTES ON (the default), the quote character, which defaults to a single quote (apostrophe), is placed around all exported strings. If QUOTES ALL is specified, the quote string is placed around all values, not just around strings. To suppress quoting, use QUOTES OFF.
ROW DELIMITED BY clause
Use this clause to specify the string that indicates the end of a record. The default delimiter string is '\x0d\x0a' (CR/LF) for Windows and '\x0a' (LF) for Unix. However, it can be any string up to up to 255 bytes in length; for example, ROW DELIMITED BY '###'. To specify tab-delimited rows, you could specify the hexadecimal escape sequence for the tab character (ordinal value 9), ROW DELIMITED BY '\x09'. If your delimiter string contains a newline character (\n), it results in a line feed (LF) character only. On Windows systems, it is customary to use '\x0d\x0a' to write out carriage return followed by line feed (CR/LF) to text files.
WITH COLUMN NAMES clause
The WITH COLUMN NAMES clause inserts the column names in the first line of the file or variable.

Remarks

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. INTO CLIENT FILE is not supported for Tabular Data Stream (TDS) connections.

When unloading table columns with binary data types, UNLOAD TABLE writes hexadecimal strings, of the form \x nnnn, 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.

When the UNLOAD statement is executed, the @@rowcount variable is set to the number of rows unloaded.

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 TIME ZONE values, set the timestamp_with_time_zone_format to YYYY-MM-DD HH:NN:SS.SSSSSS+HH:NN.

If disk sandboxing is enabled, then database operations are limited to the directory where the main database file is located.

Privileges

When unloading into a variable, no privileges are required. Otherwise, the required privileges depend on the database server -gl option, as follows:

If the -gl option is set to ALL, you must be the owner of the tables, or have SELECT privilege on the tables, or have the SELECT ANY TABLE system privilege.
If the -gl option is set to DBA, you must have the SELECT ANY TABLE system privilege.
If the -gl option is set to NONE, UNLOAD is not permitted.

When unloading to a file on a client computer:

You must have the WRITE CLIENT FILE privilege.
You must have write permissions on the directory where the file is located.
The allow_write_client_file database option must be enabled.
The WRITE_CLIENT_FILE feature must be enabled.

Side effects

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

Standards

ANSI/ISO SQL Standard
Not in the standard.

Example

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

UNLOAD TABLE GROUPO.Products TO 'c:\\temp\\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 GROUPO.Products INTO VARIABLE @myProducts ESCAPE CHARACTER '!';