Exporting query results

ASA SQL User's Guide
Importing and Exporting Data
Exporting

Exporting query results

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

You can import and export files between Adaptive Server 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)

Enter your query in the SQL Statements pane of the Interactive SQL window.
Click Execute SQL statement(s) to display the result set.
Choose Data > Export.

The Save As dialog appears.
Specify a name and location for the exported data.
Specify the file format and click Save.

If the export is successful, the Messages pane displays the amount of time it to took to export the query result set, the filename and path of the exported data, and the number of rows written.

If the export is unsuccessful, a message appears indicating the export was unsuccessful.

To export query results (Interactive SQL OUTPUT statement)

Enter your query in the SQL Statements pane of the Interactive SQL window.
At the end of the query, type OUTPUT TO 'c:\filename'.

For example, to export the entire employee table to the file employee.dbf, enter the following query:
```
SELECT *
FROM employee;
OUTPUT TO 'c:\employee.dbf'
```
If you want to export query results and append the results to another file, add the APPEND statement to the end of the OUTPUT statement.
- For example,
```
SELECT *
FROM employee;
OUTPUT TO 'c:\employee.dbf' APPEND
```
If you want to export query results and include messages, add the VERBOSE statement to the end of the OUTPUT statement.

For example,
```
SELECT *
FROM employee;
OUTPUT TO 'c:\employee.dbf' VERBOSE
```
If you want to specify a format other than ASCII, add a FORMAT clause to the end of the query.

For example,
```
SELECT *
FROM employee;
OUTPUT TO 'c:\employee.dbf'
FORMAT dbaseiii;
```
where c:\employee.dbf is the path, name, and extension of the new file and dbaseiii is the file format for this file. You can enclose the string in single or double quotation marks, but they are only required if the path contains embedded spaces.

Where dbaseiii is the file format for this file. If you leave the FORMAT option out, the file type defaults to ASCII.
Execute the statement.

If the export is successful, the Messages pane displays the amount of time it to took to export the query result set, the filename and path of the exported data, and the number of rows written. If the export is unsuccessful, a message appears indicating 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 statements to append both results and messages to an existing file. For example, type OUTPUT TO 'filename.sql' APPEND VERBOSE. For more information about APPEND and VERBOSE, see the OUTPUT statement [Interactive SQL].

The OUTPUT TO, APPEND, and VERBOSE statements 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.

To export query results (UNLOAD statement)

Execute an UNLOAD statement. For example,
```
UNLOAD
SELECT *
FROM employee;
TO 'c:\employee.dbf'
```
If the export is successful, the Messages pane displays the amount of time it to took to export the query result set, the filename and path of the exported data, and the number of rows written. If the export is unsuccessful, a message appears indicating the export was unsuccessful.