1

I've the dump of a table of a database and I have to transfer the rows on a table with the same structure on a new database. On the new table there are already some rows so I've to insert the new values only if the primary key is not already present. As database I'm using Oracle 11.6.

...
INSERT INTO TABLE1 (KEY, FIELD1, FIELD2) VALUES ('1111111','aaa','xxx');
INSERT INTO TABLE1 (KEY, FIELD1, FIELD2) VALUES ('2222222','bbb','yyy');
INSERT INTO TABLE1 (KEY, FIELD1, FIELD2) VALUES ('3333333','ccc','zzz');
...

If the key '2222222' is already in the database how can I avoid to have an error from the execution of the query?

Thank you

Ultramito
  • 25
  • 3
  • possible duplicate of [Oracle: how to UPSERT (update or insert into a table?)](http://stackoverflow.com/questions/237327/oracle-how-to-upsert-update-or-insert-into-a-table) – antlersoft Jul 03 '13 at 15:34

1 Answers1

2

You can do this for each record:

MERGE INTO TABLE1 mt
USING (
   SELECT '2222222' as KEY,
          'bbb' as FIELD1,
          'yyy' as FIELD2
   FROM dual
) t on (mt.key = t.key)
WHEN NOT MATCHED THEN
   INSERT (KEY, FIELD1, FIELD2)
   VALUES (t.KEY, t.FIELD1, t.FIELD2);

However, it might help to first insert everything in a temporary table TABLE1_TEMP. then run only one merge statement:

MERGE INTO TABLE1 mt
USING (
   SELECT KEY,         -- more interesting
          FIELD1,      -- to merge all this
          FIELD2       -- at once instead of
   FROM TABLE1_TEMP    -- just one record
) t on (mt.key = t.key)
WHEN NOT MATCHED THEN
   INSERT (KEY, FIELD1, FIELD2)
   VALUES (t.KEY, t.FIELD1, t.FIELD2);

Then drop TABLE1_TEMP

Yohan Danvin
  • 885
  • 6
  • 13
  • The solution is okay but if the record is already in the database I don't want to update/overwrite it. I need to avoid to Insert/Update if is already in the database. – Ultramito Jul 04 '13 at 09:18
  • This is exactly what the statement above does. Because there is no `WHEN MATCHED` clause, only `WHEN NOT MATCHED`. – Yohan Danvin Jul 04 '13 at 09:23