Exporting query results

You can export query results in Interactive SQL using the Data menu, or either of the OUTPUT or UNLOAD statements.

Prerequisites

None.

Context and remarks

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.

 Export query results (Interactive SQL Data menu)

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

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

  2. Click SQL » Execute.

  3. Click 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.

 Export query results (Interactive SQL OUTPUT statement)

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

  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. To export query results and append the results to another file, use the APPEND clause.

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

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

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

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

 Export query results (UNLOAD statement)

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

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

    For example:

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

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

Results

The query results are exported to the specified location.

Next

None.

 See also
Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1