Using the INSERT statement to import data

The INSERT statement lets you add rows to the database. Because you include the import data for your destination table directly in the INSERT statement, it is considered interactive input. You can also use the INSERT statement with remote data access to import data from another database rather than a file.

Use the INSERT statement to import data when you:

  • want to import small amounts of data into a single table
  • are flexible with your file formats
  • want to import remote data from an external database rather than from a file

The INSERT statement provides an ON EXISTING clause to specify the action to take if a row you are inserting is already found in the destination table. However, if you anticipate many rows qualifying for the ON EXISTING condition, consider using the MERGE statement instead. The MERGE statement provides more control over the actions you can take for matching rows. It also provides a more sophisticated syntax for defining what constitutes a match. See MERGE statement.

Considerations for materialized views

For immediate views, an error is returned when you attempt to bulk load data into an underlying table. You must truncate the data in the view first, and then perform the bulk load operation.

For manual views, you can bulk load data into an underlying table; however, the data in the view becomes stale until the next refresh.

Consider truncating data in dependent materialized views before attempting a bulk load operation such as INSERT on a table. After you have loaded the data, refresh the view. See TRUNCATE statement, and REFRESH MATERIALIZED VIEW statement.

Considerations for text indexes

For immediate text indexes, updating the text index after performing a bulk load operation such as INSERT on the underlying table can take a while even though the update is automatic. For manual text indexes, even a refresh can take a while.

Consider dropping dependent text indexes before performing a bulk load operation such as INSERT on a table. After you have loaded the data, recreate the text index. See DROP TEXT INDEX statement, and CREATE TEXT INDEX statement.

Impact on the database

Changes are recorded in the transaction log when you use the INSERT statement. This means that if there is a media failure involving the database file, you can recover information about the changes you made from the transaction log.

Example

This example shows you how to import data using the INSERT statement. With this INSERT statement, you add data to the Departments table of the SQL Anywhere sample database.

To import data (INSERT statement)

  1. Ensure that the destination table exists.

  2. Execute an INSERT statement. For example,

    The following example inserts a new row into the Departments table in the SQL Anywhere sample database.

    INSERT
INTO Departments ( DepartmentID, DepartmentName, DepartmentHeadID )
VALUES ( 700, 'Training', 501)
SELECT * FROM Departments;

    Inserting values adds the new data to the existing table.

See also
Copyright © 2008, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.0