Contents IndexExporting data from a database Tuning bulk operations

User's Guide
   Part III. Using SQL Anywhere
     Chapter 23. Importing and Exporting Data
      Importing data into a database

You can import data into your database using one of the following methods:

This section describes each of these methods, and also describes some tips for dealing with incompatible data structure and conversion errors.

Top of page


Loading data using the LOAD TABLE statement

The LOAD TABLE statement is for efficient importing of data from a text file into a database table. To use the LOAD TABLE statement, the table must exist and have the same number of columns as the input file has fields, defined on compatible data types. In order to use the LOAD TABLE statement, the user must have INSERT permission on the table.

Example

If the department table had all its rows deleted, the following statement would load the data from the file dept.txt into the department table:

     LOAD TABLE department
     FROM 'dept.txt'

The LOAD TABLE statement appends the contents of the file to the existing rows of the table; it does not replace the existing rows in the table. You can use the TRUNCATE TABLE statement to remove all the rows from a table.

Neither the TRUNCATE TABLE statement nor the LOAD TABLE statement fires triggers, including referential integrity actions such as cascaded deletes.

The LOAD TABLE statement has many of the same options as the UNLOAD TABLE statement.

For a description of column delimiters, use of quotes, and file names, see the section "Unloading data using the UNLOAD TABLE statement".

The LOAD TABLE statement has the additional STRIP clause. The default setting (STRIP ON) strips trailing blanks from values before they are inserted. To keep trailing blanks, use the STRIP OFF clause in your LOAD TABLE statement.

For a full description of the LOAD TABLE statement syntax, see "LOAD TABLE statement".

Top of page


Importing data using the ISQL INPUT statement

Data with the same structure as existing database tables can be loaded into your database from a file using the ISQL INPUT statement.

The ISQL INPUT statement is less efficient than the LOAD TABLE statement for importing text files. However, the INPUT statement supports several different file formats, whereas the LOAD TABLE statement can be used only for text files.

The INPUT command can be entered in ISQL as follows:

     INPUT INTO t1
     FROM file1
     FORMAT ASCII;
     INPUT INTO t2
     FROM file2
     FORMAT FIXED
     COLUMN WIDTHS (5, 10, 40, 40 );
     ...

These statements could be put in a command file which can then be executed in ISQL for modification and reference.

For more information about command files, see the tutorial chapter "Command Files".

Top of page


Loading data interactively

There are two commands that can be used to input data interactively. You can use the insert command:

     INSERT INTO T1 VALUES ( ... )

to insert a single row at a time or you can use the input command:

     INPUT INTO T1 PROMPT

which gives you a full screen to type in data in the current input format (controlled by the ISQL INPUT_FORMAT option).

Top of page


Handling conversion errors on data import

When you are loading data from external sources, there may be errors in the data. For example, there may be dates that are not valid dates and numbers that are not valid numbers. There is an ISQL database option (CONVERSION_ERROR) that allows you to ignore conversion errors by converting them to NULL values.

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

Top of page


Loading data that does not match the table structure

The structure of the data to be loaded into a table does not always match the structure of the destination table itself. For example, the column data types may be different, or in different order, or there may be extra values in the data to be imported that do not match columns in the destination table.

You can use a stepwise approach to load data that has a different structure to the destination table:

DECLARE TEMPORARY TABLE statement

If you are loading a set of data once and for all, you should make the temporary table using the DECLARE TEMPORARY TABLE statement. A declared temporary table exists only for the duration of a connection or, if defined inside a compound statement, of the compound statement.

CREATE TABLE statement

If you are loading data of a similar structure repeatedly, you should make the temporary table using the CREATE TABLE statement, specifying a global temporary table. The definition of a created temporary table is held in the database permanently, but the rows exist only within a given connection.

Top of page


Contents IndexExporting data from a database Tuning bulk operations