Export data with the OUTPUT statement

Use the OUTPUT statement to export query results, tables, or views from your database.

The OUTPUT statement is useful when compatibility is an issue because it can write out the result set of a SELECT statement in several different file formats. You can use the default output format, or you can specify the file format on each OUTPUT statement. Interactive SQL can execute a command file containing multiple OUTPUT statements.

The default Interactive SQL output format is specified on the Import/Export tab of the Interactive SQL Options window (accessed by choosing Tools » Options in Interactive SQL).

Use the Interactive SQL OUTPUT statement when you want to:

  • export all or part of a table or view in a format other than text

  • automate the export process using a command file

 Impact on the database
 Example
 To export data to an Excel file using the OUTPUT statement (Interactive SQL)
  1. In Interactive SQL, connect to a SQL Anywhere database.

  2. Execute an OUTPUT statement using the READONLY clause. For example:

    SELECT * FROM SalesOrders;
    OUTPUT USING 'Driver=Microsoft Excel Driver (*.xls);
    DBQ=c:\\test\\sales.xls;
    READONLY=0' INTO "newSalesData";

    A new Excel file, named sales.xls, is created. It will contain a worksheet called newSalesData.

    Note that the Microsoft Excel driver is a 32-bit driver so the 32-bit version of Interactive SQL is required for this example.

 To export data to a CSV file
  1. In Interactive SQL, connect to the SQL Anywhere database.

  2. Execute an OUTPUT statement with the clauses FORMAT TEXT, QUOTE '"', and WITH COLUMN NAMES to create a comma-delimited format with the column names in the first line of the file. String values will be enclosed with quotation marks.

    SELECT * FROM SalesOrders; 
    OUTPUT TO 'c:\\test\\sales.csv' 
        FORMAT TEXT
        QUOTE '"'
        WITH COLUMN NAMES;