| Eliminating duplicate rows while using SQL loader [message #157041] |
Mon, 30 January 2006 22:47  |
rjanaki
Messages: 1
Registered: January 2006
|
Junior Member |
|
|
Hi
I am using SQL Loader to insert records from flat file onto a table. But, some of the records in my flat file are duplicates and I need to insert only the firstly encoutered one. The records in the flat file are sorted. Is there any I can insert only the frst row enountered?
Thanks,
Janaki Ram
|
|
|
|
|
|
| Re: Eliminating duplicate rows while using SQL loader [message #157050 is a reply to message #157041] |
Tue, 31 January 2006 01:25  |
pattik
Messages: 1
Registered: January 2006
Location: S.E.QLD Australia
|
Junior Member |
|
|
Refer
restrict duplicate data through sql*loader [message #144909]
Essentially a) use unique constraint and let sqlldr reject records, danger being that you need to check to ensure no other records are also rejected
or
b) load into stage table, and use sql to insert unique records to main tables. I always try to use this approach unless doing a quick and dirty load. For production loads, I usually make the stage tables all varchar2(4000) or similar so nothing fails unless the data is really corrupt and then do validation in subsequent load. This way, errors can be generated to a stage error table and reported nicely to users simplying the data.
Regards
|
|
|
|
| Re: Eliminating duplicate rows while using SQL loader [message #157051 is a reply to message #157045] |
Tue, 31 January 2006 01:25 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
No there is no way.
You could create a unique index on the columns, then the duplicates will just fail. Note that every duplicate will write a line to the log, as well as to the BAD file, so it is not really "silent". This won't work for direct-path loads.
You could also use an Externally Organized Table (Google it), and then eliminate duplicates with a SQL statement as you insert into the target table.
_____________
Ross Leishman
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
