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

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.

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.

A very good point - I should have mentioned though that I am loading up to 50 million rows per day and therefore want to use SQL*Loader to carry out the data load. I believe that SQL*Loader will fail the entire file if it contains duplicates which violate a unique index. – user181903 Oct 21 '09 at 11:11 You can tell SQL*Loader what to do with rejected rows.

Try specifying a 'badfile' parameter on the command line, with a suitably high 'errors' parameter. – Hobo Oct 21 '09 at 11:42 @Adam - sorry, that was directed at ginsoakedboy, not you. I reckon a combination of a unique index and suitable SQL*Loader parameters is the way to go.

– Hobo Oct 21 '09 at 15:51 Thanks - I will try that out – user181903 Oct 21 '09 at 16:51.

Use Oracle MERGE statement. Some explanations here.

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).

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.

A good approach to be sure, but in order to meet my performance needs (50 million rows/day) I am using SQL*Loader to load the rows. I think that SQL*Loader will fail entire files if they contain duplicates if I add such an index, which isn't acceptable for my application. – user181903 Oct 21 '09 at 11:16.

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.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.