Hi
I have two tables namely staging and dmain. Both are very huge in size. Both has same structure. staging table contains transaction data. dmain table contains historical data. i need to update/insert into dmain table by comparing staging table records.
If existing row present with value columns change, then i need to update dmain table with staging record.
If its new record, i need to insert into dmain table.
There are few methods to achieve this. we dont have access to CDC/goldengate procedures. im thinking to do in merge statement. Is this good idea? or any other suggestions? below is my approach(im thinking if i use same table will it cause a problem).
merge into dmain
using (select s.key,s.valueCol1,s.valueCol2,s.valueCol3
from staging s left join dmain d on s.key=d.key
where (d.key is null -- for new records
or (s.valueCol1<>d.valueCol1
or s.valueCol2<>d.valueCol2
or s.valueCol3<>d.valueCol3 ) b
on dmain.key = b.key
when matched
[update dmain ] set value columns
when not matched
[insert statement]