CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldeveloper
M4rk
M4rk

Reputation: 2272

Oracle DB insert and do nothing on duplicate key

I have to insert some data in oracle DB, without previously checking if it already exist.

Does exist any way, transiction on oracle to catch the exception inside the query and handle it to don't return any exception?

It would be perfect something in mysql's style like

insert .... on duplicate key a=a

Upvotes: 3

Views: 9074

Answers (3)

volkit
volkit

Reputation: 1611

Since 11g there is the ignore_row_on_dupkey_index hint that ignores Unique Constraint-exceptions and let the script go on with the next row if there is any, see Link. The exception is not logged. It needs two arguments, the table name and the index name.

INSERT /*+ ignore_row_on_dupkey_index(my_table, my_table_idx) */
INTO my_table(id,name,phone)
    VALUES (24,'Joe','+49 19450704');

Upvotes: 4

Alen Oblak
Alen Oblak

Reputation: 3325

If you can use PL/SQL, and you have a unique index on the columns where you don't want any duplicates, then you can catch the exception and ignore it:

begin
   insert into your_table (your_col) values (your_value);
exception
   when dup_val_on_index then null;
end;

Upvotes: 4

Joachim Isaksson
Joachim Isaksson

Reputation: 180917

You can use MERGE. The syntax is a bit different from a regular insert though;

MERGE INTO test USING (
  SELECT 1 AS id, 'Test#1' AS value FROM DUAL    -- your row to insert here
) t ON (test.id = t.id)                          -- duplicate check
WHEN NOT MATCHED THEN 
   INSERT (id, value) VALUES (t.id, t.value);    -- insert if no duplicate

An SQLfiddle to test with.

Upvotes: 12

Related Questions