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.