Re: Ignore Duplicates on Insert???

I donot see any other method of doing it. If you use nologging or unrecoverable option you cannot use pl/sql. The only suggestion I can make in addition to all those you are getting is to add "commit work" just before the end. This way you wont get a rollback segment problem and it will be faster. Dave

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
Received on Tue Dec 29 1998 - 10:21:55 CST