Assuming I run an insert with a comma separated list of values for performance purposes (generally inserting 200 at a time) and the field 'id' is set as the primary key.
By default, executing the below:
Insert into table(id, somefield) values (1,'foo'),(2,'bar'),(1,'foo'),(3,'bob')
This whole insert would fail.
Is there a behavior, pragma, setting or other I can use that will allow the first two and the third inserts to succeed?
You could use insert ignore:
insert ignore into mytable (id, somefield)
values (1,'foo'),(2,'bar'),(1,'foo'),(3,'bob')
As explained in the documentation:
If you use the IGNORE modifier, ignorable errors that occur while executing the INSERT statement are ignored. For example, without IGNORE, a row that duplicates an existing UNIQUE index or PRIMARY KEY value in the table causes a duplicate-key error and the statement is aborted. With IGNORE, the row is discarded and no error occurs. Ignored errors generate warnings instead.
create table t (id int primary key, somefield varchar(10))
insert into t(id, somefield)
values (1,'foo'),(2,'bar'),(1,'foo'),(3,'bob');
-- error: Duplicate entry '1' for key 't.PRIMARY'
select * from t;
id | somefield
-: | :--------
insert ignore into t(id, somefield)
values (1,'foo'),(2,'bar'),(1,'foo'),(3,'bob');
select * from t;
id | somefield
-: | :--------
1 | foo
2 | bar
3 | bob
