Exporting tables (SQL)

You can export a table by executing an UNLOAD TABLE statement from Interactive SQL.

Prerequisites

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 secure feature must be enabled.

Context and remarks

You can also export a table by selecting all the data in a table and exporting the query results.

Task

Execute an UNLOAD TABLE statement. For example:

UNLOAD TABLE Departments
TO 'C:\\ServerTemp\\Departments.csv';

This statement unloads the Departments table from the SQL Anywhere sample database into the file Departments.csv in a directory on the database server computer, not the client computer. Since the file path is specified in a SQL literal, the backslash characters are escaped by doubling them to prevent translation of escape sequences such as '\n' or '\x'.

Each row of the table is output on a single line of the output file, and no column names are exported. The columns are delimited by a comma. The delimiter character can be changed using the DELIMITED BY clause. The fields are not fixed-width fields. Only the characters in each entry are exported, not the full width of the column.

Results

The data is exported into the specified file.

Exporting tables (SQL)

Task

See also