create a proc with dynamic sql and merge
We have two databases, the main production and the remote
db that are not normall connected through network connection.
The remote and prod db are somewhat the same in that they each have 60 tables that are the same structure and name on each db
The remote db will be loaded up with data and once in awhile
we will want to dump the data from the remote db into the prod db.
This is surely easy enough to do with the 9i MERGE statement.
My question is how to dynamically build the merge statements at run time in a pl/sql procedure
I am guessing some sort of execute immediate statement that is going against user_tab_cols.
MERGE INTO bonuses D
USING (SELECT employee_id, salary, department_id FROM employees
WHERE department_id = 80) S
ON (D.employee_id = S.employee_id)
WHEN MATCHED THEN UPDATE SET D.bonus = D.bonus + S.salary*.01
WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus)
VALUES (S.employee_id, S.salary*0.1);