使用BULK INSERT在SQL Server上插入一个空字符串 [英] Insert a empty string on SQL Server with BULK INSERT

查看:203
本文介绍了使用BULK INSERT在SQL Server上插入一个空字符串的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

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



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


1,



1,



1,''


由于批量插入。我正在使用SQL Server 2012。



我该怎么办?

解决方案

据我所知,批量插入不能插入空字符串,它可以保留null值,也可以使用带有keepnulls选项或不带有keepnulls选项的默认值。对于您的3个示例记录,在插入数据库之后,应该是:

 
| id |名称
| 1 | NULL
| 1 |
| 1 | ’’

原因是,批量插入会将第一行,第二列的值视为空;对于其他2行,将第二列的值设为非null,并保持原样。

代替让Bulk Insert为您插入空字符串值,可以让您将具有默认值的列作为空字符串。


示例如下所示:

  
创建表BulkInsertTest(id int,name varchar(10)DEFAULT'')

批量将同一CSV文件插入表中



 批量插入Adventure.dbo.BulkInsertTest 
从'.... \test.csv'


FIELDTERMINATOR ='\ \,',
ROWTERMINATOR ='\n'

SELECT * FROM BulkInsertTest

结果将如下所示((CSV的第一行将获得一个空字符串)

 
| id |名称
| 1 |
| 1 |
| 1 | ’’


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?

解决方案

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

这篇关于使用BULK INSERT在SQL Server上插入一个空字符串的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆