Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

compare data, insert where not exists.

545955Aug 1 2007 — edited Aug 1 2007
I 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???
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 29 2007
Added on Aug 1 2007
9 comments
1,064 views