Batch inserts all fail if single row fails

[alanault]

Hi,

I'm inserting a batch of 100 rows into a table using dbWriteTable. The table has a unique key to prevent duplicates and 1 of the rows I'm inserting is a duplicate to an existing entry.

In the RMySQL package, the 99 new rows would be correctly inserted, with a failure of the duplicate due to the unique constraint (although it did not return an error or warning).

However with RMariaDB, the entire insert fails, meaning no rows are added to the DB at all.

Is there a way to replicate the RMySQL behaviour or a work-around?

Many thanks

Alan

[s-fleck]

Not a developer, but this would be the expected behaviour imho. Batch inserts should not partially insert datasets into relational databases (see wikipedia for "Atomicity" principle in database transcations)

[krlmlr]

Can you try the upsert features in the dbx package?

[github-actions]

This old thread has been automatically locked. If you think you have found something related to this, please open a new issue and link to this old issue if necessary.