Use this statement to unload data from a data source into a file.
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 } | { 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 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 comma. You can
specify an alternative delimiter by providing a string up to 255 bytes in length; for example, ... ROW DELIMITED BY '###' .... The same formatting requirements apply as 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.
The UNLOAD 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 TO FILE or INTO CLIENT FILE was specified, respectively, must have operating system permissions to write to the specified file.
For UNLOAD TABLE, when unloading table columns with binary data types, UNLOAD TABLE writes hexadecimal strings, of the form \xnnnn, where n is a hexadecimal digit. For UNLOAD select-statement, when unloading result set columns with binary data types, UNLOAD writes hexadecimal strings of the form \0xnnnn, 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. See Working with external logins.
When unloading into a variable (INTO VARIABLE), the output is converted to a character set as follows:
Also, the chosen encoding must match the database CHAR encoding if the variable is of CHAR type The chosen encoding must match the database NCHAR encoding if the variable if of NCHAR type Any encoding can be used for BINARY variables
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.
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:
See -gl server option.
When writing to a file on a client computer:
None. The query is executed at the current isolation level.
SQL/2003 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; |
| Send feedback about this page via email or DocCommentXchange | Copyright © 2008, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.0 |