ASA SQL Reference
SQL Statements
Use this statement to export data from a database table into an external ASCII-format file.
UNLOAD [ FROM ] TABLE [ owner. ]table-name TO filename-string
[ unload-option ... ]
unload-option :
APPEND{ON | OFF}
| DELIMITED BY string
| ESCAPE CHARACTER character
| ESCAPES {ON | OFF}
| FORMAT {ASCII | BCP}
| HEXADECIMAL {ON | OFF}
| ORDER {ON | OFF}
| QUOTES {ON | OFF}
filename-string The filename to which the data is to be unloaded. Because it is the database server that executes the statements, filenames specify files on the database server machine. Relative filenames specify files relative to the database server's starting directory. To unload data onto a client machine, see OUTPUT statement [Interactive SQL].
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.
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 multi-byte 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.
QUOTES option With QUOTES turned on (the default), single quotes are placed around all exported strings.
The UNLOAD TABLE statement allows efficient mass exporting from a database table into an ASCII file. UNLOAD TABLE 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, DELIMITED BY, and ESCAPE CHARACTER options, see LOAD TABLE statement.
The permissions required to execute an UNLOAD TABLE statement depend on the database server -gl
command line 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.
For more information, see -gl server option.
None.
OUTPUT statement [Interactive SQL]
SQL/92 Vendor extension.
SQL/99 Vendor extension.
Sybase UNLOAD TABLE is not supported by Adaptive Server Enterprise. Similar functionality is provided by the Adaptive Server Enterprise bulk copy utility (bcp).