|
Use this statement to export data from a database table, or from a materialized view, into an external file.
UNLOAD [ FROM ] {
[ TABLE ] [ owner.]table-name
| [ MATERIALIZED VIEW ] [ owner.]materialized-view-name }
TO file-name
[ unload-option ... ]
unload-option :
APPEND { ON | OFF }
| DELIMITED BY string
| ENCODING encoding
| ESCAPE CHARACTER character
| ESCAPES { ON | OFF }
| FORMAT { ASCII | BCP }
| HEXADECIMAL { ON | OFF }
| ORDER { ON | OFF }
| QUOTE string
| QUOTES { ON | OFF }
| ROW DELIMITED BY string
file-name : { string | variable }
encoding : string
file-name The file to which the data is to be unloaded. Because the database server executes the statements, file names specify files on the database server computer. Relative file names specify files relative to the database server's starting directory. To unload data onto a client computer, see PASSTHROUGH statement [SQL Remote].
APPEND option When the APPEND option is ON, unloaded data is appended to the end of the file specified. When the APPEND option is OFF, unloaded data replaces the contents of the file specified. This option is OFF by default.
DELIMITED BY The string used between columns. The default column delimiter is a comma. You can specify an alternative column delimiter by providing a string. However, only the first byte (character) of the string is used as the delimiter.
ENCODING option 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 more information on 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';
ESCAPES option With ESCAPES on (the default), backslash-character combinations are used to identify special characters where necessary on export.
FORMAT option Outputs data in either ASCII format or in BCP out format.
HEXADECIMAL option By default, HEXADECIMAL is ON. Binary column values are written as 0xnnnnnn..., where each n is a hexadecimal digit. It is important to use HEXADECIMAL ON when dealing with multibyte character sets.
The HEXADECIMAL option can be used only with the FORMAT ASCII option.
ORDER option 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 more information on clustered indexes, see Using clustered indexes.
QUOTE option The QUOTE clause is for ASCII data only; the string is placed around string values. The default is a single quote (apostrophe).
QUOTES option With QUOTES turned on (the default), single quotes are placed around all exported strings.
ROW DELIMITED BY option 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 wanted 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 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.
UNLOAD TABLE places an exclusive lock on the whole table.
When unloading columns with binary data types, UNLOAD TABLE writes hexadecimal strings, of the form \xnnnn, where n is a hexadecimal digit.
For descriptions of the FORMAT and ESCAPE CHARACTER options, see LOAD TABLE statement.
The permissions required to execute an UNLOAD TABLE statement depend on the database server -gl option, as follows:
If the -gl option is ALL, you must have SELECT permissions on the table or tables referenced in the UNLOAD TABLE statement.
If the -gl option is DBA, you must have DBA authority.
If the -gl option is NONE, UNLOAD TABLE is not permitted.
See -gl server option.
None.
SQL/2003 Vendor extension.
The following example unloads UTF-8-encoded table data into mytable:
LOAD TABLE mytable TO 'mytable_data_in_utf8.dat' ENCODING 'UTF-8';