Insert a empty string on SQL Server with BULK INSERT

Question

Example table contains the fields Id (the Identity of the table, an integer); Name (a simple attribute that allows null values, it's a string)

I'm trying a CSV that contains this:

1,

1,""

1,''

None of them gives me a empty string as the result of the bulk insertion. I'm using SQL Server 2012.

What can I do?

Solution

As far as I know, bulk insert can't insert empty string, it can either keep null value or use default value with keepnulls option or without keepnulls option. For your 3 sample records, after insert database, it should be like:

    | id    |  name 
    | 1     |  NULL 
    | 1     |  ""   
    | 1     |  ''   

The reason is, the bulk insert will treat your first row, second column value as null; for other 2 rows, will take the second column value as not null, and take it as it is.

Instead of let Bulk Insert to insert empty string value for you, you can let you table column having default value as empty string.

Example as following:

CREATE TABLE BulkInsertTest (id int, name varchar(10) DEFAULT '')

Bulk Insert same CSV file into table

BULK INSERT Adventure.dbo.BulkInsertTest
   FROM '....\test.csv'
   WITH 
      (
         FIELDTERMINATOR ='\,',
         ROWTERMINATOR ='\n'
      )
   SELECT * FROM BulkInsertTest

The result will be like following: (The first row in your CSV will get an empty string)

    | id    |  name 
    | 1     |   
    | 1     |  ""   
    | 1     |  ''