User's Guide

Part III. Using SQL Anywhere

Chapter 23. Importing and Exporting Data

You can export data from your database using one of the following methods:

• The UNLOAD TABLE statement, for efficient export of text files
• The ISQL OUTPUT statement, for slower but more flexible export to a variety of file formats
• The DBUNLOAD utility, for text export of more than one table

This section describes each of these methods, and also describes some tips for dealing with NULL output.

Unloading data using the UNLOAD TABLE statement

The UNLOAD TABLE statement is for efficient export of data from a database table to a text file. You must have SELECT permission on the table to use the UNLOAD TABLE statement.

Example 1

The following statement unloads the department table from the sample database into the file DEPT.TXT in the current directory of the drive on which the database engine is running. If you are running against a network server, the command unloads the data into a file on the server machine, not the client machine.

     UNLOAD TABLE department
     TO 'dept.txt'

The dept.txt file has the following contents:

     100,'R & D',501
     200,'Sales',902
     300,'Finance',1293
     400,'Marketing',1576
     500,'Shipping',703

Notes

• Each row of the table is output on a single line of the output file.
• No column names are exported.
• The columns are separated, or delimited , by a comma. The delimiter character can be changed using the DELIMITED BY clause.
• The fields are not fixed-width fields. Only the number of characters in each entry are exported, not the full width of the column.
• The character data in the dept_name column is enclosed in single quotes. The single quotes can be turned off using the QUOTES clause.
• The data is exported by primary key values. This makes reloading quicker. You can export data in the order in which it is stored using the ORDER OFF clause.
• The file name is relative to the database engine's current directory, not the current directory of the client application. Also, the file name is passed to the database engine as a string, so that special characters need to be preceded by a backslash (\) in order to be recognized. For example, the following statement unloads a table into the file C:\TEMP\OUTPUT.DAT:

     UNLOAD TABLE employee TO 'c:\temp\output.dat'

For more information on the syntax, see "UNLOAD TABLE statement".

Example 2

The following example uses explicit settings for the DELIMITED BY and QUOTES clauses:

     UNLOAD TABLE department
     TO 'dept.txt'
     DELIMITED BY '$'
     QUOTES OFF

The resulting DEPT.TXT file has the following contents:

     100$R & D$501
     200$Sales$902
     300$Finance$1293
     400$Marketing$1576
     500$Shipping$703

If a delimiter character appears in a value in a column and the QUOTES option is turned off, the character is replaced by its hexadecimal value preceded by \x to prevent ambiguity as to the end of the value. For example:

     UNLOAD TABLE department
     TO 'dept.txt'
     DELIMITED BY '&'
     QUOTES OFF

yields the following output file.

     100&R \x26 D&501
     200&Sales&902
     300&Finance&1293
     400&Marketing&1576
     500&Shipping&703

Exporting data using the ISQL OUTPUT statement

Data can be exported from a database to a variety of file formats using the ISQL OUTPUT statement. This statement exports the results of the current query (the one displayed in the ISQL. data window) and puts the results into a specified file. The output format can be specified on the output command.

For example, the following commands extract the employee table to a dBaseIII format file:

     SELECT *
     FROM employee;
     OUTPUT TO employee.dbf
     FORMAT dbaseiii;

Output redirection

Output redirection can be used to export data as an alternative to the OUTPUT statement.

The output of any command can be redirected to a file or device by putting the ># redirection symbol anywhere on the command. The redirection symbol must be followed by a file name, as follows:

     ># filename

Output redirection is most useful on the SELECT statement. The SET OUTPUT_FORMAT command can be used to control the format of the output file.

Example

The >& redirection symbol redirects all output including error messages and statistics for the command on which it appears. For example:

     SELECT *
     FROM employee >& filename

outputs the SELECT statement to the file, followed by the output from the SELECT statement and some statistics pertaining to the command.

The >& redirection is useful for getting a log of what happens during a READ command. The statistics and errors of each command are written following the command in the redirected output file.

If two > characters are used in a redirection symbol instead of one (>>#, >>& .), then the output is appended to the specified file instead of replacing the contents of the file. For output from the SELECT command, headings are output if and only if the output starts at the beginning of the specified file and the output format supports headings.

NULL value output

The most common reason to extract data is for use in other software products. When your data includes NULL values, the other software package may not understand the NULL values.

There is an ISQL option (NULLS) that allows you to choose how NULL values are output. Alternatively, you can use the IFNULL function to output a specific value whenever there is a NULL value.

For information on setting ISQL options, see "SET OPTION statement".

Unloading a database using DBUNLOAD

The DBUNLOAD utility supplied with SQL Anywhere is used to unload an entire database in ASCII comma-delimited format and to create the necessary ISQL. command files to completely recreate your database. This may be useful for creating extractions, creating a backup of your database or building new copies of your database with the same or slightly modified structure.

It is particularly useful if you want to rearrange your tables in the database. Use DBUNLOAD to create the necessary command files and modify them as needed.

Exporting a list of tables

The DBUNLOAD utility can also export a list of tables, rather than the entire database. This is useful for retrieving data from a corrupted database that cannot be entirely unloaded.

The following statement unloads the data from the sample database (assumed to be running on the default database server with the default database name) into a set of files in the c: temp directory. A command file to rebuild the database from the data files is created with the default name reload.sql in the current directory.

     dbunload -c "dbn=sademo;uid=dba;pwd=sql" c:\temp

For Windows 3.x, the DBUNLOAD executable is named DBUNLOAW.EXE. For a full description of dbunload utility command-line switches, see the section "The Unload utility".