批量插入不适用于NULL数据 [英] Bulk insert not working for NULL data

问题描述

当我从CSV文件将批量数据插入表格时，它无法正常工作，显示错误：

批量加载数据转换错误

csv文件中的第9列值为null。

如何处理此问题？

解决方案

从这个信息量，我会说目标表的特定字段被定义为NOT NULL。要解决您遇到的问题：

a）修改csv - >向其中有空的字段添加值

b） 通过设置受影响的字段'nullable'来修改目标表： ALTER TABLE [tblName] ALTER COLUMN [nulColName] [varType如INT] NULL
你去找这个解决方案，并想要再次改回表格的状态：
UPDATE [tblName] SET [nulColName] = - 1000 WHERE [nulColName] IS NULL 避免更改错误，则 ALTER TABLE [tblName] ALTER COLUMN [nulColName] [varType such as INT] NOT NULL

c）非常像'b'选项，但更专业和更快：创建一个基于目标表的临时表，但允许任何和所有字段的空值，然后更新临时表的空记录在csv导入后，使用默认值和副本到目标表的数据


如果我对这个问题正确，没有修改csv的选项，我会选择'c'

When I am inserting bulk data to a table from a CSV file, it is not working, showing error lie :

Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 9

Column 9 value in csv file is null..

How do I take care of this?

解决方案

From this amount of information I'd say the target table's particular field is defined as "NOT NULL". To workaround the issue you have to:

a) modify csv-->add value to field(s) where they have null

b) modify target table by setting affected fields 'nullable':ALTER TABLE [tblName] ALTER COLUMN [nulColName] [varType such as INT] NULL
In case you go for this solution and want to turn back table's status alter it again:
UPDATE [tblName] SET [nulColName]=-1000 WHERE [nulColName] IS NULL to avoid alter errors, then ALTER TABLE [tblName] ALTER COLUMN [nulColName] [varType such as INT] NOT NULL

c) pretty much like 'b' option but a bit more professional and faster: create a temp table based on target table but allowing nulls to any and all fields, then update temp table's null records after csv import with a "default value" and copy the data to the target table


If I'm right about the issue and there's no option to revise csv I'd go for option 'c'

这篇关于批量插入不适用于NULL数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！