Insert Statements And Duplicates

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.

IGNORE_ROW_DUPKEY_INDEX(table_name, index_name)

Here’s an example:


declare
sSQL varchar2(400);
begin
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
where a.table_name=b.table_name)
loop
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||')';
dbms_output.put_line(sSQL);
execute immediate sSQL;
dbms_output.put_line(SQL%ROWCOUNT);
end loop;
end;

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.

Advertisements

Tagged: , ,

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: