The simplest method is to add a hint to the query to overcome ORA-00001 Added in 11.2, the ignore_row_on_dupkey_index hint silently ignores duplicate values:
insert /*+ ignore_row_on_dupkey_index ( acct ( username ) ) */
into accounts acct ( username, given_name )
select username, given_name from accounts_stage;
So if accounts_stage has duplicate usernames, this will add one of the rows and bypass the other.
This also works when using the values version of insert:
insert /*+ ignore_row_on_dupkey_index ( acct ( username ) ) */
into accounts acct ( username, given_name )
values ( ‘gopinath’, ‘Gopi’ );
To use the hint, put the table name or alias followed by either:
The name of a unique index
A comma-separated list of columns in a unique index
Note you can only ignore one constraint in the table. Not an issue if you want to skip extra usernames and have auto-generated primary keys.
But a limitation if you have many unique keys on a table.
While adding a hint is easy, it has a couple of other major drawbacks:
You don’t know which row will be ignored
There’s nothing to tell you that there were duplicate values
So if you want to know if there were any duplicates, you need to post-process the data to check.
If the source contains identical rows – those where every column has the same value – this may not bother you. Of course,
if this case, if you’re better off adding distinct in the subquery:
insert into accounts ( username, given_name )
select distinct username, given_name
from accounts_stage;
But if it’s only usernames that clash, it’s likely you want to know. And find out which rows you skipped. To do this you need to keep which rows failed.
