Finding duplicates across 2 tables 1

[CHUMPY]

Hi,

I know this should be basic SQL but have been struggling on this one!

I have two identical tables T1 and T2 with two fields which are composite primary keys. If I wish to insert only records from T2 into T1 that don't already exist in T1 how can I do it?

e.g.

T1 T2

A B A B
1 2 1 2
2 3 2 3
3 4 8 9



What will be the SQL to insert only the third record (8 9) from T2 into T1?

Thanks

Gareth

 

[AOLEURO]

Hi


First I create the tables

create table t1 (A number(2), B number(2));
create table t2 (a number(2), b number(2));

Now your data:

insert into t1 values( 1, 2);
insert into t1 values( 2, 3);
insert into t1 values( 3, 4);

insert into t2 values( 1, 2);
insert into t2 values( 2, 3);
insert into t2 values( 8, 9);

and now the insert statement:

insert into t1
  select * from t2
  where not exists (select 1 from t1
                      where t1.a = t2.a
                      and   t1.b = t2.b
                    );

or this statement

insert into t1                    
  select * from t2 where (a,b) not in (select a,b from t1);

Depending on the amount of data the first is faster because Oracle doesn’t generate data between joins.


Regards
Allan
Icq: 346225948

 

[Scunningham99]

you should be able to do this with the merge command

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_915a.htm#SQLRF01606

HTH

Sy UK

 

[CHUMPY]

Hi,

Thanks to both of you, I was trying something similar to Allan just seem to be having one of those days!

I wasn't aware of the merge command, wish I was. Just finished writing a tool to combine multiple schemas into one..... that command looks like I could of saved a bit of time!!!!

Thanks

Gareth