Programming

Hi ,

I have a data file in this format.


I am using a sqlloader script to load the data into the database table.There is a unique constraint on the columns p1 and p2.
So, sqlldr cannot load both the records. This eliminates duplicate records from being loaded into the DB table.
But the issue is when i load the data into the database table.
I want to be able to load the first row from the data file into the DB table as it has a value in p3 column.
I want sqlldr to reject the second row.

The database that I am working on is Oracle 11g.

Can you please let me know how can I do that ?

There is no duplicate detection in sqlldr itself. It depends on constraints defined on the the table.

You will have to prepare your inputfile. You want to reject duplicate rows, duplicate equal the first two columns in separate rows are equal. If there are differences - missing column 3 - then prefer the row with three columns, otherwise just use the first of the duplicates. You have to sort in reverse order to guarantee that, in the case of duplicates, the row with data in column 3 comes first. Then eliminate duplicates.
This does that for you:

Code:

sort -r  -o inputfile inputfile  # keep the file intact to review data if there is a problem
awk '!arr[ $1 $2 ]++' inputfile > file_to_load_into_oracle    # eliminate duplicates as defined by you