I am using the T-SQL Bulk Insert command to insert large text files into tables. The tables have many nvarchar columns, I found that if the text file has empty string values for any nvarchar column, bulk insert will insert NULL in that table column.

I have Googled this extensively, there are lots of people complaining about the reverse scenario (null >> empty string), and the bulk insert statement has a parameter to control this, but nothing to force empty string to be retained as empty string in the destination table. Seems like there should be some way to do this.

There is one solution involving setting default values on the nvarchar table columns, I don’t want to do this as the target tables are copies from a large ERP and modifying them will cause other issues potentially. Any advice is welcome!