Re: Ignore Duplicates on Insert???

In addition to this, create an index on the 4 PK columns!

joostem_at_my-dejanews.com wrote:

> Stuart
>
> 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 Tue Dec 29 1998 - 09:21:16 CET