Home » RDBMS Server » Server Utilities » SQL*Loader - How to get it to selectively overwrite existing records?
SQL*Loader - How to get it to selectively overwrite existing records? [message #257123] Tue, 07 August 2007 10:59 Go to next message
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 #257129 is a reply to message #257123] Tue, 07 August 2007 11:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use an external table and merge statement.

Regards
Michel
Re: SQL*Loader - How to get it to selectively overwrite existing records? [message #257147 is a reply to message #257129] Tue, 07 August 2007 11:44 Go to previous messageGo to next message
winehook
Messages: 4
Registered: August 2007
Junior Member
Unfortunately, I'm on Oracle 8i -- I don't think external tables are an option. Right? I thought they first came along with 9i?
Re: SQL*Loader - How to get it to selectively overwrite existing records? [message #257148 is a reply to message #257147] Tue, 07 August 2007 11:47 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Yes, External Tables Introduce in Oracle 9i,

Unfortunately, I'm on Oracle 8i --

That is why "micheal" always said " always post Oracle Version with OS"

Regards
Taj
Re: SQL*Loader - How to get it to selectively overwrite existing records? [message #257150 is a reply to message #257148] Tue, 07 August 2007 11:51 Go to previous messageGo to next message
winehook
Messages: 4
Registered: August 2007
Junior Member
Yes, you're right -- I'm sorry about that. I had intended to, but I simply forgot. My fault.

Any Oracle 8i suggestions?
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 Go to previous messageGo to next message
Littlefoot
Messages: 20895
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 Go to previous messageGo to next message
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 #257440 is a reply to message #257434] Wed, 08 August 2007 06:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I think Littlefoot's solution should be better.

Regards
Michel
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 Go to previous message
rleishman
Messages: 3724
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
Previous Topic: expdp utility hanged
Next Topic: import
Goto Forum:
  


Current Time: Tue Dec 06 00:04:22 CST 2016

Total time taken to generate the page: 0.15471 seconds