CODEWITHSUNDEEP
oracle-databaseduplicates
user181903
user181903

Reputation: 25

How do I prevent the loading of duplicate rows in to an Oracle table?

I have some large tables (millions of rows). I constantly receive files containing new rows to add in to those tables - up to 50 million rows per day. Around 0.1% of the rows I receive are duplicates of rows I have already loaded (or are duplicates within the files). I would like to prevent those rows being loaded in to the table.

I currently use SQLLoader in order to have sufficient performance to cope with my large data volume. If I take the obvious step and add a unique index on the columns which goven whether or not a row is a duplicate, SQLLoader will start to fail the entire file which contains the duplicate row - whereas I only want to prevent the duplicate row itself being loaded.

I know that in SQL Server and Sybase I can create a unique index with the 'Ignore Duplicates' property and that if I then use BCP the duplicate rows (as defined by that index) will simply not be loaded.

Is there some way to achieve the same effect in Oracle?

I do not want to remove the duplicate rows once they have been loaded - it's important to me that they should never be loaded in the first place.

Upvotes: 1

Views: 9074

Answers (5)

abhishek Kumar
abhishek Kumar

Reputation: 11

use below option, if you will get this much error 9999999 after that your sqlldr will terminate.

OPTIONS (ERRORS=9999999, DIRECT=FALSE )
LOAD DATA

you will get duplicate records in bad file.

sqlldr user/password@schema CONTROL=file.ctl, LOG=file.log, BAD=file.bad

Upvotes: 1

anon
anon

Reputation:

What do you mean by "duplicate"? If you have a column which defines a unique row you should setup a unique constraint against that column. One typically creates a unique index on this column, which will automatically setup the constraint.

EDIT: Yes, as commented below you should setup a "bad" file for SQL*Loader to capture invalid rows. But I think that establishing the unique index is probably a good idea from a data-integrity standpoint.

Upvotes: 5

Ian Carpenter
Ian Carpenter

Reputation: 8626

I would use integrity constraints defined on the appropriate table columns.

This page from the Oracle concepts manual gives an overview, if you also scroll down you will see what types of constraints are available.

Upvotes: 1

Guru
Guru

Reputation: 2371

You dint inform about what release of Oracle you have. Have a look at there for merge command.

Basically like this

---- Loop through all the rows from a record temp_emp_rec
MERGE INTO hr.employees e
     USING temp_emp_rec t
     ON (e.emp_ID = t.emp_ID)
     WHEN MATCHED THEN
    --- _You can update_
    UPDATE
     SET first_name = t.first_name,
          last_name = t.last_name
    --- _Insert into the table_
    WHEN NOT MATCHED THEN
    INSERT (emp_id, first_name, last_name)
    VALUES (t.emp_id, t.first_name, t.last_name);

Upvotes: 2

Cătălin Pitiș
Cătălin Pitiș

Reputation: 14341

Use Oracle MERGE statement. Some explanations here.

Upvotes: 2

Related Questions