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

SQL Anywhere 11.0.1 » SQL Anywhere Server - SQL Usage » Remote Data and Bulk Operations » Importing and exporting data » Exporting data

 

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

If you have a choice between using the OUTPUT statement, UNLOAD statement, or UNLOAD TABLE statement, choose the UNLOAD TABLE statement for performance reasons.

There are performance impacts associated with exporting large amounts of data with the OUTPUT statement. Use the OUTPUT statement on the same computer as the server if possible to avoid sending large amounts of data across the network.

For more information, see OUTPUT statement [Interactive SQL].

Example

The following example exports the data from the Employees table in the SQL Anywhere sample database to a .txt file named Employees.txt.

SELECT *
FROM Employees;
OUTPUT TO Employees.txt
FORMAT TEXT;

The following example exports data from the Employees table in the SQL Anywhere sample database to a new table in a SQL Anywhere database named mydatabase.db

SELECT *
FROM Employees;
OUTPUT USING 'driver=SQL Anywhere 11;UID=dba;PWD=sql;DBF=C:\Tobedeleted\mydatabase.db;CON=''''' 
INTO "dba"."newcustomers" 
CREATE TABLE ON