Unique Constraint Violation

Sometimes you have to run an insert-select statement which produces ORA-00001 "Unique Constraint Violation". Of course, that's because of an attemp to insert a duplicate key.
What to do if your select statement produces 300 rows out of which only 100 are duplicate keys and the rest you still need to insert?

One way to do that is to create a staging table & use MERGE statement to do the job. Lets say you have a table tab1 with a unique index on col1 and col2.

1. Create table tab1_bkp as select * from tab1;
2. Truncate table tab1;
3. Use your insert-select statement on tab1_bkp.
4. Merge into tab1 a using tab1_bkp b
on (a.col1=b.col1 and
a.col2=b.col2)
when not matched then
insert (col1,col2,....coln)
values (b.col1, b.col2,...b.coln);.
5. Drop tab1_bkp;

You are done