Insert if not exists Oracle

Solution 1:

Coming late to the party, but...

With oracle 11.2.0.1 there is a semantic hint that can do this: IGNORE_ROW_ON_DUPKEY_INDEX

Example:

insert /*+ IGNORE_ROW_ON_DUPKEY_INDEX(customer_orders,pk_customer_orders) */
  into customer_orders
       (order_id, customer, product)
values (    1234,     9876,  'K598')
     ;

UPDATE: Although this hint works (if you spell it correctly), there are better approaches which don't require Oracle 11R2:

First approach—direct translation of above semantic hint:

begin
  insert into customer_orders
         (order_id, customer, product)
  values (    1234,     9876,  'K698')
  ;
  commit;
exception
  when DUP_VAL_ON_INDEX
  then ROLLBACK;
end;

Second aproach—a lot faster than both above hints when there's a lot of contention:

begin
    select count (*)
    into   l_is_matching_row
    from   customer_orders
    where  order_id = 1234
    ;

    if (l_is_matching_row = 0)
    then
      insert into customer_orders
             (order_id, customer, product)
      values (    1234,     9876,  'K698')
      ;
      commit;
    end if;
exception
  when DUP_VAL_ON_INDEX
  then ROLLBACK;
end;

Solution 2:

The statement is called MERGE. Look it up, I'm too lazy.

Beware, though, that MERGE is not atomic, which could cause the following effect (thanks, Marius):

SESS1:

create table t1 (pk int primary key, i int);
create table t11 (pk int primary key, i int);
insert into t1 values(1, 1);
insert into t11 values(2, 21);
insert into t11 values(3, 31);
commit;

SESS2: insert into t1 values(2, 2);

SESS1:

MERGE INTO t1 d
USING t11 s ON (d.pk = s.pk)
WHEN NOT MATCHED THEN INSERT (d.pk, d.i) VALUES (s.pk, s.i);

SESS2: commit;

SESS1: ORA-00001

Solution 3:

This only inserts if the item to be inserted is not already present.

Works the same as:

if not exists (...) insert ... 

in T-SQL

insert into destination (DESTINATIONABBREV) 
  select 'xyz' from dual 
  left outer join destination d on d.destinationabbrev = 'xyz' 
  where d.destinationid is null;

may not be pretty, but it's handy :)