Skipping rows in the datafile that contains NULL for a column that does not allow NULLs

First published on MSDN on Sep 29, 2006

I recently ran into a customer who wanted to import data into a table that did not allow NULL for one column. However, his data file had some rows that had NULL for that column. He tried the following Bulk Insert command

bulk insert customer.dbo.test from  'c:data.txt' with (maxerrors = 20) but this command failed with the following error

Msg 4869, Level 16, State 1, Line 1
The bulk load failed. Unexpected NULL value in data file row 2, column 1. The destination column (c1) is defined as NOT NULL

Maxerror does not help in this case because the NULL enforcement is done at much later stage and this constraint cannot be disabled. Interestingly, OPENROWSET can easily handle this as follows

insert into customer.dbo.test
select * from OPENROWSET (BULK 'c:data.txt',FORMATFILE='c:format.xml') as t1
where c1 is not NULL.

Have fun with OPENROWSET.