使用BULK INSERT在SQL Server上插入空字符串

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)

示例表包含字段Id（表的标识，整数）;名称（允许空值的简单属性，它是一个字符串）

I'm trying a CSV that contains this:

我正在尝试包含以下内容的CSV：

1,

1，

1,""

1， “”

1,''

1， ''

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

由于批量插入，它们都没有给我一个空字符串。我正在使用SQL Server 2012。

What can I do?

我能做什么？

2 个解决方案

#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:

据我所知，批量插入不能插入空字符串，它可以保持空值或使用默认值与keepnulls选项或没有keepnulls选项。对于3个样本记录，在插入数据库之后，它应该是：

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

原因是，批量插入会将您的第一行，第二列值视为null;对于其他2行，将第二列值视为非空，并按原样保留。而不是让Bulk Insert为您插入空字符串值，您可以让表格列具有默认值为空字符串。示例如下：


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)

结果如下:( CSV中的第一行将获得一个空字符串）

    | 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.:

请记住，只有在未使用选项KEEPNULLS时才会插入指定的DEFAULT值。使用与上面相同的例子，如果你将选项KEEPNULLS添加到BULK INSERT，即：

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:

将导致忽略默认列值并从空字符串插入NULL，即：

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.

似乎没有充分的理由在你的例子中添加KEEPNULLS这个，但我刚刚遇到了类似的问题，其中在BULK INSERT中需要KEEPNULLS。

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.

我的解决方案是定义使staging表中的列[name] BulkInsertTest NOT NULL但是请记住，DEFAULT列值会被忽略，而是插入一个空字符串。

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

在此处查看更多内容：批量导入期间保留空值或UseDefault值（SQL Server）

#1

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


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