Exporting query results

You can export queries (including queries on views) to a file from Interactive SQL with the Data menu, the OUTPUT statement, or the UNLOAD statement.

You can import and export files between SQL Anywhere and Adaptive Server Enterprise using the BCP FORMAT clause.

For more information, see Adaptive Server Enterprise compatibility.

To export query results (Interactive SQL Data menu)

  1. Enter your query in the SQL Statements pane of Interactive SQL.

  2. Choose SQL » Execute to display the result set.

  3. Choose Data » Export.

  4. Specify a location for the results and click Next.

  5. For text, HTML, and XML files, type a file name in the File Name field and click Export.

    For an ODBC database:

    1. Select a database and click Next.
    2. Select a location to save the data and click Export.

  6. Click Close.

To export query results (Interactive SQL OUTPUT statement)

  1. Enter your query in the SQL Statements pane of Interactive SQL.

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

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

    SELECT *
FROM Employees;
OUTPUT TO 'employees.txt';

  3. Do any of the following:

    To do this... Use this clause... Example...
    Export query results and append the results to another file APPEND 
    SELECT * FROM Employees;
OUTPUT TO 'employees.txt'
APPEND;
    Export query results and include messages VERBOSE 
    SELECT * FROM Employees;
OUTPUT TO 'employees.txt' 
VERBOSE;

  4. Choose 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.

    For more information about exporting query results using the OUTPUT statement, see OUTPUT statement [Interactive SQL].

Tips

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 are equivalent to the >#, >>#, >&, and >>& operators of earlier versions of Interactive SQL. You can still use these operators to redirect data, but the new Interactive SQL statements allow for more precise output and easier to read code.

For more information about APPEND and VERBOSE, see OUTPUT statement [Interactive SQL].

To export query results (UNLOAD statement)

  1. In the SQL Statements pane, enter the UNLOAD statement. For example, 

    UNLOAD
SELECT *
FROM Employees
TO 'employee_data.csv';

  2. Choose 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.

Outputting NULLs using the OUTPUT statement

Copyright © 2008, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.0