INSERT using EXISTS clause

[gregwga50]

I have 2 files, COMMOM and COMMONBK that are indentical except COMMONBK contains some rows that are not in COMMON. How can I insert the rows into COMMON that exist in COMMONBK but not in COMMON based on 2 key fields FLD1 and FLD2. It seems I did this a long time ago using the EXISTS clause in an INSERT stmt

[wegrace]

Here ya go.

insert into common
select * from commonBk
where (fld1, fld2) not in (select fld1,fld2 from common)
;

Walt

[B.Hauser]

The folowing Statement should do the trick, i.e. it adds the rows that are in YourTable2 but NOT in YourTable1;

insert into YourTable1
      Select * from YourTable2
   Except
     Select * from YourTable1;

Birgitta

[Vectorspace]

A note for anyone who finds this post in the future.
Birgitta's method compares the whole row, not just the primary key. If the two files each had a record with the same primary key but different values for other fields, it would fail with a duplicate record error.

Another alternative (compare on primary key only, like wegrace's method):

insert into common
select * from commonBk f1
 where not exists (
    select 1 from common f2
     where f1.fld1 = f2.fld1 and f1.fld2 = f2.fld2
     fetch first 1 rows only)

This is functionally equivalent to wegrace's method, it varies which is the more efficient of the two. I expect Birgitta's method is the most efficient if you can be sure the two files do not have the duplicate record error circumstance.

[B.Hauser]

Ooopus, I read only half of the post. I overlooked that keys have to be compared.
But here an other solution:

Insert into Table2
Select x.*
   from Table2 x exception join Table1 y on x.key1 = y.Key1 and x.Key2 = y.Key2

Birgitta

[gregwga50]

these are great tips, as always.......thanks a bunch