Contents Index UNLOAD statement UPDATE statement

ASA SQL Reference
  SQL Statements

UNLOAD TABLE statement


Description 

Use this statement to export data from a database table into an external ASCII-format file.

Syntax 

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}

Parameters 

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.

Usage 

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.

Permissions 

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

For more information, see -gl server option.

Side effects 

None.

See also 

LOAD TABLE statement

OUTPUT statement [Interactive SQL]

UNLOAD statement

Standards and compatibility 

Contents Index UNLOAD statement UPDATE statement