Oracle7 Server Utilities

Loading into Empty and Non-Empty Tables

You can specify one of the following methods for loading tables:

This section describes those methods.

How Non-Empty Tables are Affected

This section corresponds to the DB2 keyword RESUME ; users of DB2 should also refer to the description of RESUME in Appendix C. If the tables you are loading already contain data, you have four choices for how SQL*Loader proceeds:

INSERT Returns an error and terminates the load if the table contains data. This option only loads data into empty tables. INSERT is the default.
APPEND Extends the table by adding new rows.
REPLACE Deletes the rows in the table and loads the new data in its place.
TRUNCATE TRUNCATEs the table and loads the new data in place of the old.

Warning: When the REPLACE or TRUNCATE keyword is specified, the entire table is replaced, not individual rows. After the rows are successfully deleted, a commit is issued. You cannot recover the data that was in the table before the load, unless it was saved with Export or a comparable utility.

The remainder of this section provides additional detail on these options.

INSERT

This is the default method. It requires the table to be empty before loading. SQL*Loader terminates with an error if the table contains rows. Case 1

has an example.

APPEND

If data already exists in the table, SQL*Loader appends the new rows to it. If data doesn't already exist, the new rows are simply loaded. Case 3

has an example.

REPLACE

All rows in the table are deleted and the new data is loaded. The table must be in your schema, or you must have DELETE privilege on the table. Case 4

has an example.

The row deletes cause any delete triggers defined on the table to fire. If DELETE CASCADE has been specified for the table, then the cascaded deletes are carried out, as well. For more information on cascaded deletes, see the "Data Integrity" chapter of the Oracle7 Server Concepts manual.

Updating Existing Rows

The REPLACE method is a table replacement, not a replacement of individual rows. SQL*Loader does not update existing records, even if they have null columns. To update existing rows, use the following procedure:

1. Load your data into a temporary table.

2. Use the SQL language UPDATE statement with correlated subqueries.

3. Drop the temporary table. For more information, see the "UPDATE" statement in the Oracle7 Server SQL Reference.

TRUNCATE

With this method, SQL*Loader uses the SQL TRUNCATE command to achieve the best possible performance. For the TRUNCATE command to operate, the table's referential integrity constraints must first be disabled. If they have not been disabled, SQL*Loader returns an error.

Once the integrity constraints have been disabled, DELETE CASCADE is no longer defined for the table. If the DELETE CASCADE functionality is needed, then the contents of the table must be manually deleted before the load begins.

The table must be in your schema, or you must have the DELETE ANY TABLE privilege.

Specifying One Method for All Tables

You specify one table-loading method that applies to all tables by placing the keyword before any INTO TABLE clauses. This choice applies to any table that does not have its own method. You can specify a table-loading method for a single table by including the keyword in the INTO TABLE clause, as described in "Loading Logical Records into Tables"

INSERT	Returns an error and terminates the load if the table contains data. This option only loads data into empty tables. INSERT is the default.
APPEND	Extends the table by adding new rows.
REPLACE	Deletes the rows in the table and loads the new data in its place.
TRUNCATE	TRUNCATEs the table and loads the new data in place of the old.