0

I have insert statement contains multiple rows of values, like:

 INSERT INTO table1 VALUES ('xxx','xxxx'), ('xx','xxxx'), ... ;

I wonder if there is any features like MySQL INSERT IGNORE in Oracle, which silently ignores the rows with duplicated primary key values.

I know there is MERGE, but I don't want the data source to be selected from other tables. My data source is in the literals after VALUES.

kaya3
  • 47,440
  • 4
  • 68
  • 97
Richard Dong
  • 704
  • 1
  • 7
  • 13
  • 1
    Oracle doesn't let you supply multiple sets of values like that. And merge can use data selected from dual, though it's not ideal. Where is your data coming from, and which version of Oracle are you using? – Alex Poole May 18 '16 at 13:41
  • @mathguy - merge wouldn't tolerate a violation, but it would let you avoid it, if the `on` uses the PK columns. You'd only insert when they are not matched, and the potential violators would be ignored (assuming no update clause of course). Also 11g has the `ignore_row_on_dupkey_index` hint, and then there's an error logging table, so various options depending on the DB version. – Alex Poole May 18 '16 at 14:04
  • Oh - I thought the OP wants to insert the rows anyway. My bad - I don't know what INSERT IGNORE does in MySQL. Does it simply ignore the duplicates (instead of inserting them)? –  May 18 '16 at 14:06
  • @mathguy - no idea really, but that's what the second paragraph of the question suggests - "silently ignores the rows", which I took to mean it doesn't insert them but also doesn't complain... Although [from the docs](https://dev.mysql.com/doc/refman/5.5/en/insert.html) it ignores any error, not just duplicate keys, and discards the bad data. – Alex Poole May 18 '16 at 14:11
  • OK, I checked the MySQL documentation, and indeed INSERT IGNORE discards rows that would cause any kind of error. I will remove my comment, it may mislead readers in the future. –  May 18 '16 at 14:17

0 Answers0