SQL不会使用BULK INSERT插入空值 [英] SQL won't insert null values with BULK INSERT

问题描述

我有一个CSV文件，每一行看起来都与此类似:

I have a CSV file and each line looks similar to this:

EASTTEXAS,NULL,BELLVILLE AREA,NULL,BELLVILLE AREA,RGP,NULL,NULL,0,NULL,NULL,NULL,1,1,PM,PM Settings,NULL,NULL

我找不到任何关于在执行BULK INSERT时应该如何处理NULL值的示例，所以我认为这是可以的.

I couldn't find any examples on how NULL values were supposed to be handled when doing BULK INSERT, so I assumed that was OK.

当我尝试运行BULK INSERT时，它给了我这个错误:

When I try to run the BULK INSERT, it gives me this error:

4864州第1级第28行的消息4864 第1行第12列(ClassificationPK)的批量加载数据转换错误(类型不匹配或指定代码页的字符无效).

Msg 4864, Level 16, State 1, Line 28 Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 12 (ClassificationPK).

这是我的桌子，什么不是:

Here's my table and what not:

CREATE TABLE #Assets
(
ParentID VARCHAR(255) NULL,
ClassificationID VARCHAR(255) NULL,
AssetID VARCHAR(255) NULL,
AssetType VARCHAR(255) NULL,
AssetName VARCHAR(255) NULL,
RepairCenterID VARCHAR(255) NULL,
LaborID VARCHAR(255) NULL,
Owner VARCHAR(255) NULL,
IsLocation VARCHAR(255) NULL,
AssetTypeDesc VARCHAR(255) NULL,
ClassificationName VARCHAR(255) NULL,
ClassificationPK INT NULL,
IsUp BIT NULL,
RequesterCanView BIT NULL,
PMCycleStartBy VARCHAR(255) NULL,
PMCycleStartByDesc VARCHAR(255) NULL,
PMCycleStartDate DATETIME NULL,
PMCounter INT NULL,
ParentPK INT NULL,
ParentName VARCHAR(255) NULL,
AssetPK INT NULL,
RepairCenterPK INT NULL,
RepairCenterName VARCHAR(255) NULL,
LaborPK INT NULL)

BULK
INSERT #Assets
FROM '\\cdmsqlint01\drop\assets.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n',
KEEPNULLS
)
GO

SELECT * FROM #Assets

DROP TABLE #Assets

对我在这里做错的任何想法吗?

Any ideas on what I'm doing wrong here?

推荐答案

根据:

http://msdn.microsoft.com/en-us/library/ms187887.aspx

空值.

示例文件为:

1,,DataField3
2,,DataField3

导入保留空值的文件的示例方法是:

Example method of importing file keeping nulls is:

USE AdventureWorks;
GO
BULK INSERT MyTestDefaultCol2
FROM 'C:\MyTestEmptyField2-c.Dat'
WITH (
    DATAFILETYPE = 'char',
    FIELDTERMINATOR = ',',
    KEEPNULLS
);
GO

当然，这意味着您必须将"NULL"更改为"，并且您想要作为空字符串的任何空字符串都将被解释为null，但这足以让您入门吗?我可以想象要保留您的空字符串列，它们将需要从中更改

Granted, this means you'll have to change your "NULL"s to "", and any empty strings you did want as empty string would be interpreted as nulls, but it might be enough to get you started? I would imagine to keep your empty string columns they would need to be changed from

field1,,field2

到

field1,"",field2

例如

这篇关于SQL不会使用BULK INSERT插入空值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！