Loading Data into Nonempty Tables

If the tables you are loading into already contain data, then you have three options:

  • APPEND

  • REPLACE

  • TRUNCATE

    Note:

    When REPLACE or TRUNCATE is specified, the entire table is replaced, not just individual rows. After the rows are successfully deleted, a COMMIT statement 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.

APPEND

If data already exists in the table, then SQL*Loader appends the new rows to it. If data does not already exist, then the new rows are simply loaded. You must have SELECT privilege to use the APPEND option. Case study 3, Loading a Delimited Free-Format File, provides an example. (See "SQL*Loader Case Studies" for information on how to access case studies.)

REPLACE

The REPLACE option executes a SQL DELETE FROM TABLE statement. 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 study 4, Loading Combined Physical Records, provides an example. (See "SQL*Loader Case Studies" for information on how to access case studies.)

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. For more information about cascaded deletes, see Oracle Database Concepts.

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 work table.
  2. Use the SQL UPDATE statement with correlated subqueries.
  3. Drop the work table.

TRUNCATE

The TRUNCATE option executes a SQL TRUNCATE TABLE table_name REUSE STORAGE statement, which means that the table's extents will be reused. The TRUNCATE option quickly and efficiently deletes all rows from a table or cluster, to achieve the best possible performance. For the TRUNCATE statement to operate, the table's referential integrity constraints must first be disabled. If they have not been disabled, then 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 DROP ANY TABLE privilege.