Xref: alice comp.databases.oracle.server:36861 Path: alice!news-feed.fnsi.net!netnews.com!howland.erols.net!remarQ73!supernews.com!news.winstar.net!winstar!news.good.net!news.phoenix.good.net!news.goodnet.com!not-for-mail From: "Pat Minnis" Newsgroups: comp.databases.oracle.server References: <768pu9$4l4$1@nnrp1.dejanews.com> Subject: Re: Ignore Duplicates on Insert??? Lines: 41 X-Newsreader: Microsoft Outlook Express 4.72.3155.0 X-MimeOLE: Produced By Microsoft MimeOLE V4.72.3155.0 Message-ID: Date: Fri, 1 Jan 1999 13:59:26 -0600 X-Trace: news.goodnet.com 915220958 208.15.174.140 (Fri, 01 Jan 1999 13:02:38 MDT) NNTP-Posting-Date: Fri, 01 Jan 1999 13:02:38 MDT 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@mailcity.com wrote in message <768pu9$4l4$1@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