Oracle insert if row does not exist

Note that if you are lucky enough to work with version 11g Release 2, you can use the hint IGNORE_ROW_ON_DUPKEY_INDEX.

INSERT /*+ IGNORE_ROW_ON_DUPKEY_INDEX(table1(id)) */ INTO table1 SELECT ...

From the documentation: http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/sql_elements006.htm#CHDEGDDG

An example from my blog: http://rwijk.blogspot.com/2009/10/three-new-hints.html

Regards, Rob.

When I run this I get the error "missing INTO keyword" .

Because IGNORE is not a keyword in Oracle. That is MySQL syntax.

What you can do is use MERGE.

merge into table1 t1
    using (select 'value1' as value1 ,value2 
           from table2 
           where table2.type = 'ok' ) t2
    on ( t1.value1 = t2.value1)
when not matched then
   insert values (t2.value1, t2.value2)
/

From Oracle 10g we can use merge without handling both branches. In 9i we had to use a "dummy" MATCHED branch.

In more ancient versions the only options were either :

1. test for the row's existence before issuing an INSERT (or in a sub-query);
2. to use PL/SQL to execute the INSERT and handle any resultant DUP_VAL_ON_INDEX error.