I have two schema and tables where tables get updated every weekly. my concern is I use the merge statement and updated it and at the point of time it looks fine and tables are been matched. later week when the table got update the table count is mismatching again. so what would be the best solution.
example
staging table : select * from emp -- 500
source table : select * from emp1-600
So I used
Merge into schema.emp a using (select * from emp1) aa on(a.deptno = aa.deptno) when matched then update set (---) and when not matched then Insert(----) values(-----) ;
delete from emp1 where pk in (select pk from emp1 minus select pk from emp);
commit;
as a sample it has only few records in general millions of records so table gets mismatched every week.