Insert if not exists Oracle

Question

I need to be able to run an Oracle query which goes to insert a number of rows  but it also checks to see if a primary key exists and if it does  then it skips that insert  Something like  INSERT ALL     IF NOT EXISTS  SELECT 1 WHERE fo primary key  bar                  INSERT INTO              schema myFoo fo   primary key  value1  value2           VALUES               bar   baz   bat                   IF NOT EXISTS  SELECT 1 WHERE fo primary key  bar1                  INSERT INTO              schema myFoo fo   primary key  value1  value2           VALUES               bar1   baz1   bat1         SELECT   FROM schema myFoo   Is this at all possible with Oracle  Bonus points if you can tell me how to do this in PostgreSQL or MySQL

User · Answer

It that code is on the client then you have many trips to the server so to eliminate that.

Insert all the data into a temportary table say T with the same structure as myFoo

Then

insert myFoo
  select *
     from t
       where t.primary_key not in ( select primary_key from myFoo)

This should work on other databases as well - I have done this on Sybase

It is not the best if very few of the new data is to be inserted as you have copied all the data over the wire.

User · Answer

We can combine the DUAL and NOT EXISTS to archive your requirement   INSERT INTO schema myFoo        primary key  value1  value2    SELECT      bar    baz    bat   FROM DUAL WHERE NOT EXISTS       SELECT 1      FROM schema myFoo     WHERE primary key    bar

User · Answer

If you do NOT want to merge in from an other table  but rather insert new data    I came up with this  Is there perhaps a better way to do this    MERGE INTO TABLE1 a     USING DUAL     ON  a C1 pk  6  WHEN NOT MATCHED THEN     INSERT C1 pk  C2 C3 C4      VALUES  6  1 0 1

User · Answer

If your table is  independent  from others  I mean  it will not trigger a cascade delete or will not set any foreign keys relations to null   a nice trick could be to first DELETE the row and then INSERT it again  It could go like this   DELETE FROM MyTable WHERE prop1    aaa     assuming it will select at most one row   INSERT INTO MyTable  prop1       VALUES   aaa          If your are deleting something which does not exist  nothing will happen

User · Answer

DECLARE    tmp NUMBER 3 1   BEGIN   SELECT COUNT content id  INTO tmp FROM contents WHERE  condition     if tmp    0 then     INSERT INTO contents VALUES          else     INSERT INTO contents VALUES          end if  END    I used the code above  It is long  but  simple and worked for me  Similar  to Micheal s code

User · Answer

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

User · Answer

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 mdash 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 mdash 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

User · Answer

This is an answer to the comment posted by erikkallen      You don t need a temp table  If you   only have a few rows   SELECT 1 FROM   dual UNION SELECT 2 FROM dual  will   do  Why would your example give   ORA-0001  Wouldn t merge take the   update lock on the index key and not   continue until Sess1 has either   committed or rolled back      erikkallen   Well  try it yourself and tell me whether you get the same error or not   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

User · Answer

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

User · Answer

INSERT INTO schema myFoo   primary key          value1            value2                                    SELECT  bar1  AS primary key   baz1  AS value1   bat1  AS value2 FROM DUAL WHERE  SELECT 1 AS value FROM schema myFoo WHERE LOWER primary key    bar1  AND ROWNUM 1  is null

[sql] Insert if not exists Oracle

Examples related to sql

Examples related to oracle

Examples related to oracle-sqldeveloper