mysql ignore oracle,Oracle Equivalent to MySQL INSERT IGNORE?

I need to update a query so that it checks that a duplicate entry does not exist before insertion. In MySQL I can just use INSERT IGNORE so that if a duplicate record is found it just skips the insert, but I can't seem to find an equivalent option for Oracle. Any suggestions?

# Answer 1

Check out the MERGE statement. This should do what you want - it's the WHEN NOT MATCHED clause that will do this.

Do to Oracle's lack of support for a true VALUES() clause the syntax for a single record with fixed values is pretty clumsy though:

MERGE INTO your_table yt

USING (

SELECT 42 as the_pk_value,

'some_value' as some_column

FROM dual

) t on (yt.pk = t.the_pke_value)

WHEN NOT MATCHED THEN

INSERT (pk, the_column)

VALUES (t.the_pk_value, t.some_column);

A different approach (if you are e.g. doing bulk loading from a different table) is to use the "Error logging" facility of Oracle. The statement would look like this:

INSERT INTO your_table (col1, col2, col3)

SELECT c1, c2, c3

FROM staging_table

LOG ERRORS INTO errlog ('some comment') REJECT LIMIT UNLIMITED;

Afterwards all rows that would have thrown an error are available in the table errlog. You need to create that errlog table (or whatever name you choose) manually before running the insert using DBMS_ERRLOG.CREATE_ERROR_LOG.

See the manual for details

# Answer 2

If you're on 11g you can use the hint IGNORE_ROW_ON_DUPKEY_INDEX:

SQL> create table my_table(a number, constraint my_table_pk primary key (a));

Table created.

SQL> insert /*+ ignore_row_on_dupkey_index(my_table, my_table_pk) */

2 into my_table

3 select 1 from dual

4 union all

5 select 1 from dual;

1 row created.

# Answer 3

I don't think there is but to save time you can attempt the insert and ignore the inevitable error:

begin

insert into table_a( col1, col2, col3 )

values ( 1, 2, 3 );

exception when dup_val_on_index then

null;

end;

/

This will only ignore exceptions raised specifically by duplicate primary key or unique key constraints; everything else will be raised as normal.

If you don't want to do this then you have to select from the table first, which isn't really that efficient.

# Answer 4

Another variant

Insert into my_table (student_id, group_id)

select distinct p.studentid, g.groupid

from person p, group g

where NOT EXISTS (select 1

from my_table a

where a.student_id = p.studentid

and a.group_id = g.groupid)

or you could do

Insert into my_table (student_id, group_id)

select distinct p.studentid, g.groupid

from person p, group g

MINUS

select student_id, group_id

from my_table

# Answer 5

A simple solution

insert into t1

select from t2

where not exists

(select 1 from t1 where t1.id= t2.id)

# Answer 6

This one isn't mine, but came in really handy when using sqlloader:

create a view that points to your table:

CREATE OR REPLACE VIEW test_view

AS SELECT * FROM test_tab

create the trigger:

CREATE OR REPLACE TRIGGER test_trig

INSTEAD OF INSERT ON test_view

FOR EACH ROW

BEGIN

INSERT INTO test_tab VALUES

(:NEW.id, :NEW.name);

EXCEPTION

WHEN DUP_VAL_ON_INDEX THEN NULL;

END test_trig;

and in the ctl file, insert into the view instead:

OPTIONS(ERRORS=0)

LOAD DATA

INFILE 'file_with_duplicates.csv'

INTO TABLE test_view

FIELDS TERMINATED BY ','

(id, field1)

# Answer 7

How about simply adding an index with whatever fields you need to check for dupes on and say it must be unique? Saves a read check.

# Answer 8

yet another "where not exists"-variant using dual...

insert into t1(id, unique_name)

select t1_seq.nextval, 'Franz-Xaver' from dual

where not exists (select 1 from t1 where unique_name = 'Franz-Xaver');