Insert a empty string on SQL Server with BULK INSE

问题:

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?

回答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:

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

回答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
1   NULL
1   ""
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 BulkInsertTest NOT 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)