Home » SQL & PL/SQL » SQL & PL/SQL » Procedure to avoid duplicates
Procedure to avoid duplicates [message #222106] Thu, 01 March 2007 19:13 Go to next message
kavi123
Messages: 13
Registered: January 2007
Junior Member
Hi,
I have to insert 100 records into table1 from table2 and both the tables have file number as common column.
I have to check each record of table2 with 100 records present in table1 with matching column file number.
If table1 does not contain matching record with table2 then insert the record into table1 else insert into exception table.

for eg: Table 1
123 karen jane
142 joseph crest
134 diane jones

Table 2
143 george fransis
156 kate windson
142 joseph crest

Then output of Table 1 should be
123 karen jane
142 joseph crest
134 diane jones
143 george fransis
156 kate windson

exception table
142 joseph crest.

Can anyone help me on this.

Thanku,
Have a Great day and weekend,
Kavi

Re: Procedure to avoid duplicates [message #222107 is a reply to message #222106] Thu, 01 March 2007 19:18 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
I'd use the MERGE command
Re: Procedure to avoid duplicates [message #222250 is a reply to message #222106] Fri, 02 March 2007 08:53 Go to previous messageGo to next message
kavi123
Messages: 13
Registered: January 2007
Junior Member
Iam using Oracle 8i and so I cannot use MERGE command.

Can anyone help on this.

Thanku,
Have a Great day,
kavitha.
Re: Procedure to avoid duplicates [message #222253 is a reply to message #222250] Fri, 02 March 2007 09:09 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
-- This will insert into the exceptions table all the rows
-- from table_2 that have matching rows in table_1
INSERT INTO exceptions (<column_list>)
SELECT <column_list> 
FROM   table_2
INTERSECT
SELECT <column_list>
FROM   table_1;

-- This will insert into Table 1 all the rows from table 2
-- that don't have matching rows in table_1
INSERT INTO table_1 (<column_list>) 
SELECT <column_list> 
FROM   table_2
MINUS
SELECT <column_list>
FROM   table_1;

DELETE Table_2

Previous Topic: SQL records display
Next Topic: Loading the .csv file into the database table
Goto Forum:
  


Current Time: Wed Dec 07 18:57:32 CST 2016

Total time taken to generate the page: 0.11430 seconds