Re: Ignore Duplicates on Insert???
Date: Mon, 28 Dec 1998 22:48:29 GMT
Message-ID: <7691rt$b8p$1_at_nnrp1.dejanews.com>
Stuart
[Quoted] Create the primary key on table2 with the the added clause EXCEPTIONS INTO <<your_exception_tablename_here>>
The exception table can be created with a utility script and looks like this:
Name Null? Type ------------------------------- -------- ---- ROW_ID ROWID OWNER VARCHAR2(30) TABLE_NAME VARCHAR2(30) CONSTRAINT VARCHAR2(30)and if the script is used it will be named EXCEPTIONS
Be sure to have enough space to create all the duplicates in that table.
Your insert will now:
- load all unique records into table2 without blowing up
- load info about the duplicates in the exception table
You can then use row_id in the exceptions table to delete from table1 by comparing the rowid of table1 to row_id in the exceptions table.
It should work nicely.
Good luck
Marius Jooste
joostem_at_dteenergy.com
In article <768pve$4la$1_at_nnrp1.dejanews.com>,
stuco_at_mailcity.com wrote:
> I have a 3 million-row table that has a bunch of duplicates and no Primary
> Key. I need to insert all of the rows from that table into another table of
> identical structure with a Primary Key.
>
> Here's the PL/SQL I am using:
>
> BEGIN
> INSERT INTO table1
> SELECT * FROM table2;
> EXCEPTION
> WHEN DUP_VAL_ON_INDEX THEN NULL;
> END;
>
> My goal is to insert all unique rows as defined by the Primary Key and ignore
> duplicates, so that the insert continues when it hits a constraint violation.
>
> I have tried to delete duplicates, but the key is four columns and a mere
> select takes hours.
>
> Thanks to all in advance!
>
> Stuart L. Cowen
> Paladin Consulting
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
>
-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Mon Dec 28 1998 - 23:48:29 CET