Exporting query results using the OUTPUT statement

You can export query results in Interactive SQL by using the OUTPUT statement.

Prérequis

You must be the owner of the table you are querying, have SELECT privilege on the table, or have the SELECT ANY TABLE system privilege.

Contexte et remarques

You can combine the APPEND and VERBOSE clauses to append both results and messages to an existing file. For example, type OUTPUT TO 'filename' APPEND VERBOSE.

The OUTPUT statement with its clauses APPEND and VERBOSE is equivalent to the >#, >>#, >&, and >>& operators of earlier versions of Interactive SQL. You can use these operators to redirect data, but the Interactive SQL statements allow for more precise output and easier-to-read code.

Use the BCP FORMAT clause to import and export files between SQL Anywhere and Adaptive Server Enterprise.

Task

Type your query in the SQL Statements pane of Interactive SQL.

At the end of the query, type OUTPUT TO 'filename'.

For example, to export the entire Employees table to the file Employees.csv, enter the following query:

SELECT * FROM Employees;
OUTPUT TO 'C:\\LocalTemp\\Employees.csv';

To export query results and append the results to another file, use the APPEND clause. For example:

SELECT * FROM Employees;
OUTPUT TO 'C:\\LocalTemp\\Employees.csv'
APPEND;

To export query results and include messages, use the VERBOSE clause. For example:

SELECT * FROM Employees;
OUTPUT TO 'C:\\LocalTemp\\Employees.csv' 
VERBOSE;

Click SQL » Execute.

If the export is successful, the Messages tab displays the amount of time it to took to export the query result set, the file name and path of the exported data, and the number of rows written. If the export is unsuccessful, a message appears indicating that the export was unsuccessful.

Résultat

The query results are exported to the specified location.

Exporting query results using the OUTPUT statement

Task

See also