The data is partially incorrect in a table in development and they want the data brought over from QA. The caveat, as there always is, only copy the data that is unique. Unique by the primary key, all columns or some columns? In any of the cases a special query needs to be written to select the distinct values for the columns that they wish to use for uniqueness. There should be a better way. MySQL has the Insert Ignore statement that will ignore duplicates why can’t Oracle. It does! It’s implemented as a hint.
Here’s an example:
for tab in
(select a.table_name,b.constraint_name from
(select table_name from dba_constraints
where r_constraint_name = 'TAB_PK') a,
(select constraint_name,table_name from dba_constraints
where constraint_type = 'P') b
sSQL:='insert /*+ ignore_row_on_dupkey_index('||tab.table_name||','||tab.constraint_name||')*/ into claims.'||tab.table_name||' (select * from claims.'||tab.table_name||'@hmqa01 minus select * from claims.'||tab.table_name||')';
execute immediate sSQL;
I prefer the dynamic sql, it allows me to utilize dbms_output to capture the statements. This is handy when you need to provide the developer with the statements that were ran, as well as capturing the number of rows. Yes I know I didn’t include exception handling.
What a time saving hint.