Oracle 'INSERT ALL' ignore duplicates

Question

I have a database table with a unique constraint on it (unique (DADSNBR, DAROLEID) pair). I am going to be inserting multiple values into this table simultaneously, so I'd like to get it done using one query - I'm assuming this would be the faster way. My query is thus:

INSERT ALL
    INTO ACCESS (DADSNBR, DAROLEID) VALUES (68, 1)
    INTO ACCESS (DADSNBR, DAROLEID) VALUES (68, 2)
    INTO ACCESS (DADSNBR, DAROLEID) VALUES (68, 3)
    INTO ACCESS (DADSNBR, DAROLEID) VALUES (68, 4)
SELECT 1 FROM DUAL

Since there are some entries within the statement that are duplicates of those already in the database, the whole insert fails and none of the rows are inserted.

Is there a way to ignore the cases where the unique constraint fails, and just insert the ones that are unique, without having to split it up into individual INSERT statements?

Edit: I realised I probably don't want to do this anyway, but I'm still curious as to whether it's possible or not.

Answer 1

Use the MERGE statement to handle this situation:

merge into "ACCESS" a
using
( 
   select 68 as DADSNBR,1 as DAROLEID from dual union all
   select 68,2 from dual union all
   select 68,3 from dual union all
   select 68,4 from dual
) t 
on (t.DADSNBR = a.DADSNBR and t.DAROLEID = a.DAROLEID)
when not matched then 
  insert (DADSNBR, DAROLEID)
  values (t.DADSNBR, t.DAROLEID);