Handling Empty Strings in SQL Bulk Insert

I have now made some investigations, and it seems that you need to look elsewhere. Or change your strategy.

BULK INSERT insists on interpreting an empty field as the empty string when the target column is not nullable. If the column is nullable, it will insert NULL.

If you add a CHECK constraint, it does not help setting the batchsize to 1, becase BULK INSERT gives up the first error.

BCP interprets the empty string as NULL, even if the column does not accept NULL. But if there is a NULL value, the entire file is lost. You can set the batchsize to 1, but then only the rows before the first error is imported.

So either set the column as nullable, and then look for the errors with a query. Or use SSIS....

---

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se