Bulk insert without errors

Recently, someone posted a question to Ask SQL Server Central:
https://ask.sqlservercentral.com/questions/135586/eliminate-last-row.html

The issue was that the last row in a text file contained the row count, so he needed to bulk import all but that last row.

My solution was to set maxerrors to 1 so the import would ignore the last row due to the error. Any other row with an error would still fail the import. This reminded me of one of my least favorite defaults in SQL Server, so I decided to write about it here as well. A lot of people don’t realize this, but by default a bulk insert will only fail after 10 errors. Why not 0? I’ve never understood that. If you don’t change the default and then bulk insert 20 rows of data from a file, it will only fail if over half of the rows cause an error.

I generally recommend setting maxerrors to 0. In this case, I suggested setting it to 1. He can expect the last line to fail, so setting it to 1 means he’ll be okay as long as that’s the only bad line. Any rows with errors fewer than the limit are simply ignored.

Here is how to use the option for each of the 3 bulk insert methods.

BULK INSERT … WITH (MAXERRORS = 1);

SELECT * FROM OPENROWSET(BULK …, MAXERRORS = 1);

bcp -m 1

I suggest you use this option (with 0 or another appropriate value) in all bulk inserts to override the default of 10 … unless you’re okay ignoring the first 10 errors.