Insert A Empty String On Sql Server With Bulk Insert

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:

Solution 1:

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:

CREATETABLE 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     |  ''   

Solution 2:

Please bear in mind that the specified DEFAULT value will only get inserted if you are not using the option KEEPNULLS. Using the same example as above, if you add the option KEEPNULLS to the BULK INSERT, i.e.:

BULK INSERT BulkInsertTest
FROM'....\test.csv'WITH 
(
    FIELDTERMINATOR ='\,',
    ROWTERMINATOR ='\n',
    KEEPNULLS
)

will result in the default column value being ignored and NULLs being inserted fro empty strings, i.e:

SELECT*FROM BulkInsertTest

will now give you:

id  name
1NULL1""1''

There does not seem to be a good reason to add KEEPNULLS this in your example, but I came across a similar problem just now, where KEEPNULLS was required in the BULK INSERT.

My solution was to define make the column [name] in the staging table BulkInsertTestNOT NULL but remember that the DEFAULT column value gets ignored and an empty string gets inserted instead.

See more here : Keep Nulls or UseDefault Values During Bulk Import (SQL Server)