<< Importing and exporting data | Bulk import and export of large objects >>

Bulk import and export requirements

<< Importing and exporting data | Bulk import and export of large objects >>

Derby Server and Administration Guide

read the first line of the file to determine the number of columns, then read the file again
to import the data.

Note: The import and export procedures are server-side utilities that exhibit different
behavior in client/server mode. Typically, you use these procedures to import data into
and export data from a locally running Derby database. However, you can use the import
and export procedures when Derby is running in a server framework if you specify import
and export files that are accessible to the server.

Bulk import and export requirements and considerations

There are requirements and limitations that you must consider before you use the Derby
import and export procedures.

Database transactions

You should issue either a COMMIT or ROLLBACK statement to complete all
transactions and release all table-level locks before you invoke an import or export
procedure. Derby issues a COMMIT or a ROLLBACK statement after each import
and export procedure is run.
Note: Imports are transactional. If an error occurs during bulk import, all changes are
rolled back.

Database connections

To invoke a Derby import or export procedure, you must be connected to the
database into which the data is imported or from which the data is exported. Other
user applications that access the table with a separate connection do not need to
disconnect.

Classpath

You must have the

derbytools.jar

file in your classpath before you can use the

import or export procedures from

The table must exist

To import data into a table, the table must already exist in Derby. The table does not
have to be empty. If the table is not empty, bulk import performs single row inserts,
which result in slower performance.

Create indexes, keys, and unique constraints before you import

To avoid a separate step, create the indexes, keys (primary and foreign), and unique
constraints on tables before you import data. However, if your memory and disk
space resources are limited, you can build the indexes and primary keys after
importing data.

Data types

Derby implicitly converts the strings to the data type of the receiving column. If any of
the implicit conversions fail, the whole import is aborted. For example, "3+7" cannot
be converted into an integer. An export that encounters a runtime error stops.
Note: You cannot import or export the XML data type.

Locking during import

Import procedures use the same isolation level as the connection in which they are
executed to insert data into tables. During import, the entire table is exclusively locked
irrespective of the isolation level.

Locking during export

Export procedures use the same isolation level as the connection in which they are
executed to fetch data from tables.

Import behavior on tables with triggers

The import procedures enable INSERT triggers when data is appended to the table.
The REPLACE parameter is not allowed when triggers are enabled on the table.

Restrictions on the REPLACE parameter

If you import data into a table that already contains data, you can either replace
or append to the existing data. You can use the REPLACE parameter on tables
that have dependent tables. The replaced data must maintain referential integrity;