compare data, insert where not exists.
545955Aug 1 2007 — edited Aug 1 2007I am having trouble remembering how to do a comparison using where not exists.
I have a difficult senario where I need to make 2 tables identical in rows. Therefore I need to compare each table (which are the same table structure on different databases) and insert any missing rows from one another. I attempted using MERGE but that was unsuccessful. I played with UNION ALL, but that did not work either.
I remember an old friend many years ago had the same issue and used a WHERE NOT EXISTS clause to do the inserts. You can assume a standard temp table scenario- the primary key is an ID field and a date field.
Let's assume the tables are TEMP
insert into temp
select * from temp a
where not exists (select * from temp@otherdb.world b
where a.id != b.id
and a.rec_date != b.rec_date);
This did not work, my test tables had values I knew where different and should have been inserted, but this gave me 0 rows.
Does anyone have any success comparing and inserting missing rows???