Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 10.0.1 » SQL Anywhere Server - SQL Usage » Importing and Exporting Data » Exporting data from databases

Using the Unload Data dialog Next Page

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. Click Execute SQL statement(s) to display the result set.

  3. From the Data menu, choose Export.

    The Save As dialog appears.

  4. Specify a name and location for the exported data.

  5. Specify the file format, and then click OK.

    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.

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 'file-name'.

    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 fileAPPEND
    SELECT * FROM Employees;
    OUTPUT TO 'employees.txt'
    APPEND;
    Export query results and include messagesVERBOSE
    SELECT * FROM Employees;
    OUTPUT TO 'employees.txt' 
    VERBOSE;
    Specify a file format other than ASCII (the default)FORMAT
    SELECT * FROM Employees; 
    OUTPUT TO 'employees.dbf' 
    FORMAT DBASEIII;

    Thus, employees.dbf is the name and extension of the new file and DBASEIII is the file format. You can enclose the string in single or double quotation marks, but they are only required if the file path or name contain embedded spaces.

    If you leave the FORMAT option out, the file type defaults to ASCII.

  4. Execute the statement by choosing Execute from the SQL menu.

    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 'file-name' 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. Execute the UNLOAD statement by choosing Execute from the SQL menu.

    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.


  3. Outputting NULLs using the OUTPUT statement