Oracle Equivalent to MySQL INSERT IGNORE?

Question

I need to update a query so that it checks that a duplicate entry does not exist before insertion. In MySQL I can just use INSERT IGNORE so that if a duplicate record is found it just skips the insert, but I can't seem to find an equivalent option for Oracle. Any suggestions?

Answer 1

Check out the MERGE statement. This should do what you want - it's the WHEN NOT MATCHED clause that will do this.

Do to Oracle's lack of support for a true VALUES() clause the syntax for a single record with fixed values is pretty clumsy though:

MERGE INTO your_table yt
USING (
   SELECT 42 as the_pk_value, 
          'some_value' as some_column
   FROM dual
) t on (yt.pk = t.the_pke_value) 
WHEN NOT MATCHED THEN 
   INSERT (pk, the_column)
   VALUES (t.the_pk_value, t.some_column);

A different approach (if you are e.g. doing bulk loading from a different table) is to use the "Error logging" facility of Oracle. The statement would look like this:

 INSERT INTO your_table (col1, col2, col3)
 SELECT c1, c2, c3
 FROM staging_table
 LOG ERRORS INTO errlog ('some comment') REJECT LIMIT UNLIMITED;

Afterwards all rows that would have thrown an error are available in the table errlog. You need to create that errlog table (or whatever name you choose) manually before running the insert using DBMS_ERRLOG.CREATE_ERROR_LOG.

See the manual for details

Answer 2

If you're on 11g you can use the hint IGNORE_ROW_ON_DUPKEY_INDEX:

SQL> create table my_table(a number, constraint my_table_pk primary key (a));

Table created.

SQL> insert /*+ ignore_row_on_dupkey_index(my_table, my_table_pk) */
  2  into my_table
  3  select 1 from dual
  4  union all
  5  select 1 from dual;

1 row created.

Answer 3

I don't think there is but to save time you can attempt the insert and ignore the inevitable error:

begin

   insert into table_a( col1, col2, col3 )
   values ( 1, 2, 3 );

   exception when dup_val_on_index then 
      null;

end;
/

This will only ignore exceptions raised specifically by duplicate primary key or unique key constraints; everything else will be raised as normal.

If you don't want to do this then you have to select from the table first, which isn't really that efficient.

Answer 4

Another variant

Insert into my_table (student_id, group_id)
select distinct p.studentid, g.groupid 
from person p, group g
where NOT EXISTS (select 1
                 from my_table a
                 where a.student_id = p.studentid
                 and a.group_id = g.groupid)

or you could do

Insert into my_table (student_id, group_id)
select distinct p.studentid, g.groupid 
from person p, group g
MINUS
select student_id, group_id
from my_table 

Answer 5

A simple solution

insert into t1
  select from t2 
  where not exists 
    (select 1 from t1 where t1.id= t2.id)

Answer 6

This one isn't mine, but came in really handy when using sqlloader:

1. create a view that points to your table:

   CREATE OR REPLACE VIEW test_view
   AS SELECT * FROM test_tab
   

2. create the trigger:

   CREATE OR REPLACE TRIGGER test_trig
    INSTEAD OF INSERT ON test_view
    FOR EACH ROW
     BEGIN
      INSERT INTO test_tab VALUES
       (:NEW.id, :NEW.name);
     EXCEPTION
      WHEN DUP_VAL_ON_INDEX THEN NULL;
     END test_trig;
   

3. and in the ctl file, insert into the view instead:

   OPTIONS(ERRORS=0)
   LOAD DATA
   INFILE 'file_with_duplicates.csv'
   INTO TABLE test_view
   FIELDS TERMINATED BY ','
   (id, field1)
   

Answer 7

yet another "where not exists"-variant using dual...

insert into t1(id, unique_name)
  select t1_seq.nextval, 'Franz-Xaver' from dual 
    where not exists (select 1 from t1 where unique_name = 'Franz-Xaver');

Answer 8

How about simply adding an index with whatever fields you need to check for dupes on and say it must be unique? Saves a read check.