| SQL*Loader - How to get it to selectively overwrite existing records? [message #257123] |
Tue, 07 August 2007 10:59  |
winehook
Messages: 4
Registered: August 2007
|
Junior Member |
|
|
I know that this is very, very bad database form, but I'm stuck in a business situation where I need to have SQL*Loader selectively overwrite records for me.
I've got one large table that contains a whole bunch of data, all with a single primary key. We do regular incremental updates into this database (using APPEND) with SQL*Loader. But now these incremental updates are going to regularly contain records intended to overwrite data that already exists in the table. I'm going to be getting data with the same primary key value as some records already in the database.
How do I get SQL*Loader to delete the existing record, then import the new record, when it encounters these duplicate primary keys?
|
|
|
|
|
|
|
|
|
|
|
|
| Re: SQL*Loader - How to get it to selectively overwrite existing records? [message #257191 is a reply to message #257150] |
Tue, 07 August 2007 13:15  |
 |
Littlefoot
Messages: 21811
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
If it is a true primary key (the one you created, not only mentioned in your post), no problem - you won't be able to insert another record with the same primary key column value. Those records will be rejected due to primary key constraint violation.
P.S. It means, though, that the old record(s) will remain in a table (instead of new ones).
Another option would be simulating external table feature: create a temporary table (which would look exactly as original one). Load the whole file into it using SQL*Loader's REPLACE option. Once it is done, call a (PL/)SQL script which would distinguish duplicate records, delete existing one and insert a new record.
[Updated on: Tue, 07 August 2007 13:18] Report message to a moderator |
|
|
|
| Re: SQL*Loader - How to get it to selectively overwrite existing records? [message #257434 is a reply to message #257191] |
Wed, 08 August 2007 06:34 |
winehook
Messages: 4
Registered: August 2007
|
Junior Member |
|
|
I thought about it some more -- what I think I'm going to do maybe do is this:
1) Remove the primary key constraint on my unique ID field.
2) This way, duplicate IDs will be imported just fine
3) Write a PL/SQL trigger that runs after each import, and have that trigger look for duplicate IDs, and copy the oldest record for each ID pair to a backup table before deleting the record.
Make sense to anybody else?
|
|
|
|
|
|
| Re: SQL*Loader - How to get it to selectively overwrite existing records? [message #257795 is a reply to message #257440] |
Thu, 09 August 2007 03:18 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
I think Littlefoots idea is better too.
If you like bad ideas though, I have a bad idea that is not as bad as yours.
You could create a view over the table with an INSTEAD OF INSERT trigger that performed the PL/SQL functionality you were after. Then just load into the view.
I still like LF's idea better.
Ross Leishman
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
