Oracle "INSERT ALL" ignores duplicates

Question

I have a database table with a unique restriction on it (unique pair (DADSNBR, DAROLEID) ). I am going to insert several values ​​into this table at the same time, so I would like to do this with a single query - I assume it will be faster. So my query is:

 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 records in the statement that are duplicates of those that are already in the database, the entire insert fails and none of the rows are inserted.

Is there a way to ignore cases where a unique constraint fails, and simply insert those that are unique, without having to split them into separate INSERT statements?

Edit: I realized that I probably don't want to do this, but I'm still wondering if this is possible or not.

Answer 1

In Oracle, statements are either fully completed or fully completed (they are atomic). However, you can add suggestions in some cases to register exceptions instead of raising errors:

• using BULK COLLECT - SAVE EXCEPTIONS , as shown in this thread in askTom ,
• or using DBMS_ERRLOG (available from 10g, I think).

The second method is all automatic, here is a demo (using 11gR2):

 SQL> CREATE TABLE test (pk1 NUMBER, 2 pk2 NUMBER, 3 CONSTRAINT pk_test PRIMARY KEY (pk1, pk2)); Table created. SQL> /* Statement fails because of duplicate */ SQL> INSERT into test (SELECT 1, 1 FROM dual CONNECT BY LEVEL <= 2); ERROR at line 1: ORA-00001: unique constraint (VNZ.PK_TEST) violated SQL> BEGIN dbms_errlog.create_error_log('TEST'); END; 2 / PL/SQL procedure successfully completed. SQL> /* Statement succeeds and the error will be logged */ SQL> INSERT into test (SELECT 1, 1 FROM dual CONNECT BY LEVEL <= 2) 2 LOG ERRORS REJECT LIMIT UNLIMITED; 1 row(s) inserted. SQL> select ORA_ERR_MESG$, pk1, pk2 from err$_test; ORA_ERR_MESG$ PK1 PK2 --------------------------------------------------- --- --- ORA-00001: unique constraint (VNZ.PK_TEST) violated 1 1 

You can use the LOG ERROR clause with INSERT ALL (thanks @Alex Poole ), but you must add a clause after each table:

 SQL> INSERT ALL 2 INTO test VALUES (1, 1) LOG ERRORS REJECT LIMIT UNLIMITED 3 INTO test VALUES (1, 1) LOG ERRORS REJECT LIMIT UNLIMITED 4 (SELECT * FROM dual); 0 row(s) inserted. 

Answer 2

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);