Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Ignore Duplicates on Insert???
I believe there might be a quick and easy way. I am spouting this off the
top of my head without a reference book, so it may not work :-)
Disable the primary key of the table you're copying into.
Enable the primary key using the exceptions into clause -- requires
pre-creation of an exceptions table.
This will allow all duplicates to be removed automatically.
(Of course, you have no control over which row to keep.)
stuco_at_mailcity.com wrote in message <768pu9$4l4$1_at_nnrp1.dejanews.com>...
>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
Received on Fri Jan 01 1999 - 13:59:26 CST