RE: PL/SQL Question:Eliminate duplicate rows
Ron, Doesn't matter. On the way to your car, or the gym, or to watch your kids play ball - my Uncle Guido can find you anywhere you go!! :) Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, September 19, 2003 5:20 PM To: Multiple recipients of list ORACLE-L Hi Tom- Submitting to a listserve is like living in a small town. Make 1 little oops and everybody knows about it. Do I need to look over my shoulder on the way to my car tonight??? ;) Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan [EMAIL PROTECTED] e.ny.us To: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED]Subject: RE: PL/SQL Question:Eliminate duplicate rows .com 09/19/2003 02:29 PM Please respond to ORACLE-L You know, I never use that exception, so I can't remember it correctly. You are correct, of course - thanks for embarrasing me in front of thousands and thousands and thousands (how many Jared??) of people! :) Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, September 19, 2003 4:10 PM To: Multiple recipients of list ORACLE-L except your too_many_rows exception should be dup_val_on_index... Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan [EMAIL PROTECTED] e.ny.us To: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED]Subject: RE: PL/SQL Question:Eliminate duplicate rows .com 09/19/2003 01:54 PM Please respond to ORACLE-L Johann, how about the following. what this does is, using the inner begin/end block, catches when an insert would fail because of the PK failure and ignores the error. This is very quick and dirty - it will work fine if you are not working with a huge amount of data. declare cursor c1 is select col1, col2 from some_table; begin for c1_rec in c1 loop begin insert into new_table(col1, col2) values (c1_rec.col1, c1_rec.col2); exception when too_many_rows then null; end; end loop; end; / If you are talking about lots and lots of data, you could easily query the table you are inserting into, testing for the existence of the value you are attempting to insert. If you find it, skip the insert. Like this: declare rec_count number; cursor c1 is select col1, col2 from some_table; begin for c1_rec in c1 loop begin select count(*) into rec_count from new_table where col1 = c1_rec.col1; -- this assumes that col1 is the pk! if rec_count = 0 then insert into new_table(col1, col2) values (c1_rec.col1, c1_rec.col2); end if; exception when too_many_rows then null; end; end loop; end; / Good Luck! Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, September 19, 2003 3:05 PM To: Multiple recipients of list ORACLE-L Looking for an a sample cursor routine to load a PK enabled table to eliminate any dupes from the load table. __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Johan Muller INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information
RE: PL/SQL Question:Eliminate duplicate rows
Job well done, Tom, the embarrasment is of no consequence. The routine was modified to include Ron's pointer. Stats for Tom's first routine below: load table 1.3 million rows, results table (deduped rows) 48,307. Completion time (via sqlplus over network) 63 seconds. Second routine (same tables) : 21 seconds! (Oracle 8.1.7 on AIX 4.3 IBM M80 (6 way Risc 6000 4 Gig Ram 1.2 TB IBM Shark attached array) --- Mercadante, Thomas F [EMAIL PROTECTED] wrote: You know, I never use that exception, so I can't remember it correctly. You are correct, of course - thanks for embarrasing me in front of thousands and thousands and thousands (how many Jared??) of people! :) Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, September 19, 2003 4:10 PM To: Multiple recipients of list ORACLE-L except your too_many_rows exception should be dup_val_on_index... Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan [EMAIL PROTECTED] e.ny.us To: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED]Subject: RE: PL/SQL Question:Eliminate duplicate rows .com 09/19/2003 01:54 PM Please respond to ORACLE-L Johann, how about the following. what this does is, using the inner begin/end block, catches when an insert would fail because of the PK failure and ignores the error. This is very quick and dirty - it will work fine if you are not working with a huge amount of data. declare cursor c1 is select col1, col2 from some_table; begin for c1_rec in c1 loop begin insert into new_table(col1, col2) values (c1_rec.col1, c1_rec.col2); exception when too_many_rows then null; end; end loop; end; / If you are talking about lots and lots of data, you could easily query the table you are inserting into, testing for the existence of the value you are attempting to insert. If you find it, skip the insert. Like this: declare rec_count number; cursor c1 is select col1, col2 from some_table; begin for c1_rec in c1 loop begin select count(*) into rec_count from new_table where col1 = c1_rec.col1; -- this assumes that col1 is the pk! if rec_count = 0 then insert into new_table(col1, col2) values (c1_rec.col1, c1_rec.col2); end if; exception when too_many_rows then null; end; end loop; end; / Good Luck! Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, September 19, 2003 3:05 PM To: Multiple recipients of list ORACLE-L Looking for an a sample cursor routine to load a PK enabled table to eliminate any dupes from the load table. __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Johan Muller INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ron Thomas INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL
RE: PL/SQL Question:Eliminate duplicate rows
Johann, how about the following. what this does is, using the inner begin/end block, catches when an insert would fail because of the PK failure and ignores the error. This is very quick and dirty - it will work fine if you are not working with a huge amount of data. declare cursor c1 is select col1, col2 from some_table; begin for c1_rec in c1 loop begin insert into new_table(col1, col2) values (c1_rec.col1, c1_rec.col2); exception when too_many_rows then null; end; end loop; end; / If you are talking about lots and lots of data, you could easily query the table you are inserting into, testing for the existence of the value you are attempting to insert. If you find it, skip the insert. Like this: declare rec_count number; cursor c1 is select col1, col2 from some_table; begin for c1_rec in c1 loop begin select count(*) into rec_count from new_table where col1 = c1_rec.col1; -- this assumes that col1 is the pk! if rec_count = 0 then insert into new_table(col1, col2) values (c1_rec.col1, c1_rec.col2); end if; exception when too_many_rows then null; end; end loop; end; / Good Luck! Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, September 19, 2003 3:05 PM To: Multiple recipients of list ORACLE-L Looking for an a sample cursor routine to load a PK enabled table to eliminate any dupes from the load table. __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Johan Muller INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: PL/SQL Question:Eliminate duplicate rows
Easy way: delete from table where rowid not in (select max(rowid) from table group by PK); Complicated way: Alter table mytab enable constraint PK exceptions into exceptions; Then, you should see how many rows are duplicated and use the method 1 on that set of rowids. If the table in question is a multi-gigabyte table and the number of rows is relatively small, then the second method is much, much faster. -- Mladen Gogala Oracle DBA -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Johan Muller Sent: Friday, September 19, 2003 3:05 PM To: Multiple recipients of list ORACLE-L Subject: PL/SQL Question:Eliminate duplicate rows Looking for an a sample cursor routine to load a PK enabled table to eliminate any dupes from the load table. __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Johan Muller INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: PL/SQL Question:Eliminate duplicate rows
except your too_many_rows exception should be dup_val_on_index... Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan [EMAIL PROTECTED] e.ny.us To: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED]Subject: RE: PL/SQL Question:Eliminate duplicate rows .com 09/19/2003 01:54 PM Please respond to ORACLE-L Johann, how about the following. what this does is, using the inner begin/end block, catches when an insert would fail because of the PK failure and ignores the error. This is very quick and dirty - it will work fine if you are not working with a huge amount of data. declare cursor c1 is select col1, col2 from some_table; begin for c1_rec in c1 loop begin insert into new_table(col1, col2) values (c1_rec.col1, c1_rec.col2); exception when too_many_rows then null; end; end loop; end; / If you are talking about lots and lots of data, you could easily query the table you are inserting into, testing for the existence of the value you are attempting to insert. If you find it, skip the insert. Like this: declare rec_count number; cursor c1 is select col1, col2 from some_table; begin for c1_rec in c1 loop begin select count(*) into rec_count from new_table where col1 = c1_rec.col1; -- this assumes that col1 is the pk! if rec_count = 0 then insert into new_table(col1, col2) values (c1_rec.col1, c1_rec.col2); end if; exception when too_many_rows then null; end; end loop; end; / Good Luck! Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, September 19, 2003 3:05 PM To: Multiple recipients of list ORACLE-L Looking for an a sample cursor routine to load a PK enabled table to eliminate any dupes from the load table. __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Johan Muller INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru
RE: PL/SQL Question:Eliminate duplicate rows
You know, I never use that exception, so I can't remember it correctly. You are correct, of course - thanks for embarrasing me in front of thousands and thousands and thousands (how many Jared??) of people! :) Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, September 19, 2003 4:10 PM To: Multiple recipients of list ORACLE-L except your too_many_rows exception should be dup_val_on_index... Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan [EMAIL PROTECTED] e.ny.us To: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED]Subject: RE: PL/SQL Question:Eliminate duplicate rows .com 09/19/2003 01:54 PM Please respond to ORACLE-L Johann, how about the following. what this does is, using the inner begin/end block, catches when an insert would fail because of the PK failure and ignores the error. This is very quick and dirty - it will work fine if you are not working with a huge amount of data. declare cursor c1 is select col1, col2 from some_table; begin for c1_rec in c1 loop begin insert into new_table(col1, col2) values (c1_rec.col1, c1_rec.col2); exception when too_many_rows then null; end; end loop; end; / If you are talking about lots and lots of data, you could easily query the table you are inserting into, testing for the existence of the value you are attempting to insert. If you find it, skip the insert. Like this: declare rec_count number; cursor c1 is select col1, col2 from some_table; begin for c1_rec in c1 loop begin select count(*) into rec_count from new_table where col1 = c1_rec.col1; -- this assumes that col1 is the pk! if rec_count = 0 then insert into new_table(col1, col2) values (c1_rec.col1, c1_rec.col2); end if; exception when too_many_rows then null; end; end loop; end; / Good Luck! Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, September 19, 2003 3:05 PM To: Multiple recipients of list ORACLE-L Looking for an a sample cursor routine to load a PK enabled table to eliminate any dupes from the load table. __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Johan Muller INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ron Thomas INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E
RE: PL/SQL Question:Eliminate duplicate rows
Check SQL Reference for exception_clause when creating Primary Key. Could help to do what you need just using SQL (no PL/SQL). Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Mercadante, Thomas F Sent: Friday, September 19, 2003 2:55 PM To: Multiple recipients of list ORACLE-L Johann, how about the following. what this does is, using the inner begin/end block, catches when an insert would fail because of the PK failure and ignores the error. This is very quick and dirty - it will work fine if you are not working with a huge amount of data. declare cursor c1 is select col1, col2 from some_table; begin for c1_rec in c1 loop begin insert into new_table(col1, col2) values (c1_rec.col1, c1_rec.col2); exception when too_many_rows then null; end; end loop; end; / If you are talking about lots and lots of data, you could easily query the table you are inserting into, testing for the existence of the value you are attempting to insert. If you find it, skip the insert. Like this: declare rec_count number; cursor c1 is select col1, col2 from some_table; begin for c1_rec in c1 loop begin select count(*) into rec_count from new_table where col1 = c1_rec.col1; -- this assumes that col1 is the pk! if rec_count = 0 then insert into new_table(col1, col2) values (c1_rec.col1, c1_rec.col2); end if; exception when too_many_rows then null; end; end loop; end; / Good Luck! Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, September 19, 2003 3:05 PM To: Multiple recipients of list ORACLE-L Looking for an a sample cursor routine to load a PK enabled table to eliminate any dupes from the load table. __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Johan Muller INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: PL/SQL Question:Eliminate duplicate rows
Hi Tom- Submitting to a listserve is like living in a small town. Make 1 little oops and everybody knows about it. Do I need to look over my shoulder on the way to my car tonight??? ;) Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan [EMAIL PROTECTED] e.ny.us To: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED]Subject: RE: PL/SQL Question:Eliminate duplicate rows .com 09/19/2003 02:29 PM Please respond to ORACLE-L You know, I never use that exception, so I can't remember it correctly. You are correct, of course - thanks for embarrasing me in front of thousands and thousands and thousands (how many Jared??) of people! :) Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, September 19, 2003 4:10 PM To: Multiple recipients of list ORACLE-L except your too_many_rows exception should be dup_val_on_index... Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan [EMAIL PROTECTED] e.ny.us To: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED]Subject: RE: PL/SQL Question:Eliminate duplicate rows .com 09/19/2003 01:54 PM Please respond to ORACLE-L Johann, how about the following. what this does is, using the inner begin/end block, catches when an insert would fail because of the PK failure and ignores the error. This is very quick and dirty - it will work fine if you are not working with a huge amount of data. declare cursor c1 is select col1, col2 from some_table; begin for c1_rec in c1 loop begin insert into new_table(col1, col2) values (c1_rec.col1, c1_rec.col2); exception when too_many_rows then null; end; end loop; end; / If you are talking about lots and lots of data, you could easily query the table you are inserting into, testing for the existence of the value you are attempting to insert. If you find it, skip the insert. Like this: declare rec_count number; cursor c1 is select col1, col2 from some_table; begin for c1_rec in c1 loop begin select count(*) into rec_count from new_table where col1 = c1_rec.col1; -- this assumes that col1 is the pk! if rec_count = 0 then insert into new_table(col1, col2) values (c1_rec.col1, c1_rec.col2); end if; exception when too_many_rows then null; end; end loop; end; / Good Luck! Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, September 19, 2003 3:05 PM To: Multiple recipients of list ORACLE-L Looking for an a sample cursor routine to load a PK enabled table to eliminate any dupes from the load table. __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http
