Re: Ignore Duplicates on Insert???

From: <sjakobson_at_my-dejanews.com>
Date: Mon, 28 Dec 1998 22:11:00 GMT
Message-ID: <768vlj$9c9$1@nnrp1.dejanews.com>

All you need is:

BEGIN
  FOR rec IN (SELECT * FROM table2) LOOP     BEGIN

      INSERT INTO table1 VALUES(rec.column1,...,rec.columnN);
     EXCEPTION
      WHEN DUP_VAL_ON_INDEX THEN NULL;

    END;
  END LOOP;
END;
/

or if you have a lot of columns you can use:

BEGIN
  FOR rec IN (SELECT rowid FROM table2) LOOP     BEGIN

      INSERT INTO table1 SELECT * FROM table2 WHERE rowid = rec.rowid;
     EXCEPTION
      WHEN DUP_VAL_ON_INDEX THEN NULL;

    END;
  END LOOP;
END;
/

Solomon Yakobson.

In article <768psu$4dq$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 - 16:11:00 CST