Re: sqlldr

On Tue, 20 Feb 2001 17:30:19 -0000, "yoonna park" <yoonna.park_at_blueberry.net> wrote:

>When using sqlldr, I get error
>
>SQL*Loader-601: For insert option, table must be empty.
>
>
>Surely, it would be possible to load data into non-empty table, wouldn't it.
>
>Help.
>
>yoon
>

From the docs

Loading into Empty Tables
If the tables you are loading into are empty, use the INSERT option.

Loading into Non-Empty Tables
If the tables you are loading into already contain data, you have three options:

APPEND REPLACE TRUNCATE Warning: When the REPLACE or TRUNCATE keyword is specified, the entire table is replaced, not just 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.

Note: This section corresponds to the DB2 keyword RESUME; users of DB2 should also refer to the description of RESUME in Appendix B, "DB2/DXT User Notes".

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. You must have SELECT privilege to use the APPEND option. Case 3: Loading a Delimited, Free-Format File provides 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: Loading Combined Physical Records provides 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 Oracle8i 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:

Load your data into a work table.

Use the SQL language UPDATE statement with correlated subqueries.

Drop the work table.

For more information, see the "UPDATE" statement in Oracle8i SQL Reference.

TRUNCATE
Using 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.

Notes:

Unlike the SQL TRUNCATE option, this method re-uses a table's extents.

INSERT is SQL*Loader's default method. It requires the table to be empty before loading. SQL*Loader terminates with an error if the table contains rows. Case 1: Loading Variable-Length Data provides an example

Hth,

Sybrand Bakker, Oracle DBA Received on Wed Feb 21 2001 - 02:01:05 CST