Oracle Equivalent to MySQL INSERT IGNORE?
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 ytUSING ( 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
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.
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.