ASA SQL User's Guide
Importing and Exporting Data
Exporting
There are a variety of tools available to help you export your data.
You can export data from Interactive SQL by choosing Export from the Data menu. This allows you to choose the format of the exported query results.
You can export query results, tables or views from your database using the Interactive SQL OUTPUT statement. The Interactive SQL OUTPUT statement supports several different file formats. You can either specify 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.
There are performance impacts associated with exporting large amounts of data with the OUTPUT statement. As well, you should use the OUTPUT statement on the same machine as the server if possible to avoid sending large amounts of data across the network.
Choose the Interactive SQL OUTPUT statement when you want to export all or part of a table or view in a format other than text, or when you want to automate the export process using a command file.
For more information, see OUTPUT statement [Interactive SQL].
You execute the UNLOAD TABLE statement from the SQL Statements pane of the Interactive SQL window. It allows you to export data only, in an efficient manner in text/ASCII/FIXED formats. The UNLOAD TABLE statement exports with one row per line, and values separated by a comma delimiter. The data exports in order by primary key values to make reloading quicker.
Choose the UNLOAD TABLE statement when you want to export entire tables in text format. If you have a choice between using the OUTPUT statement, UNLOAD statement, or UNLOAD TABLE statement, choose the UNLOAD TABLE statement for performance reasons.
For more information, see UNLOAD TABLE statement.
The UNLOAD statement is similar to the OUTPUT statement in that they both export query results to a file. The UNLOAD statement, however, allows you to export data in a more efficient manner and in text/ASCII/FIXED formats only. The UNLOAD statement exports with one row per line, with values separated by a comma delimiter.
To use the UNLOAD statement, the user must have ALTER or SELECT permission on the table. For more information about controlling who can use the UNLOAD statement, see -gl server option.
Choose the UNLOAD statement when you want to export query results if performance is an issue, and if output in text format is acceptable. The UNLOAD statement is also a good choice when you want to embed an export command in an application.
When unloading and reloading a database that has proxy tables, you must create an external login to map the local user to the remote user, even if the user has the same password on both the local and remote databases. If you do not have an external login, the reload may fail because you cannot connect to the remote server.
For more information, see UNLOAD statement.
The dbunload utility and Sybase Central are graphically different, and functionally equivalent. You can use either one interchangeably to produce the same results. These tools are different from Interactive SQL statements in that they can operate on several tables at once. And in addition to exporting table data, both tools can also export table schema.
If you want to rearrange your tables in the database, you can use dbunload to create the necessary command files and modify them as needed. Sybase Central provides wizards and a GUI interface for unloading one, many, or all of the tables in a database, and dbunload provides command line options for the same activities. Tables can be unloaded with structure only, data only or both structure and data. To unload fewer than all of the tables in a database, a connection must be established beforehand.
You can also extract one or many tables with or without command files. These files can be used to create identical tables in different databases.
Choose Sybase Central or the dbunload utility when you want to export in text format, when you need to process large amounts of data quickly, when your file format requirements are flexible, or when your database needs to be rebuilt or extracted.
For more information, see Unloading a database using the dbunload command-line utility.