Here’s my experience on MySQL, for what it’s worth.
This is much faster :
insert into table (col1,col2) values
(val1,val2),
(val3,val4),
(val5,val6)
etc
than this :
insert into table (col1,col2) values (val1,val2)
insert into table (col1,col2) values (val3,val4)
insert into table (col1,col2) values (val5,val6)
but the first one will fail to insert all rows should one of them violate a constraint.
load data infile … is, I think, marginally the fastest of all. I sometimes write all my inserts to a temp file and use this method to insert them.
So it depends on what you are inserting (and how many records), along with how you want it to fail when things go wrong.
A note - I regularly insert upwards of 10k rows at a time. Worst case is between 1 to 10 million, but to be fair, that’s not all that common in a single insert.