不能作为空值SQL Server 2008 TSV文件导入吗

I import data from a TSV file with SQL Server 2008.

我使用SQL Server 2008从TSV文件导入数据。

null is replaced by 0 when I confirm a table after import　with integer column.

当我用整列导入后确认一个表时，null被0替换。

How to import as null, please Help me!!

如何导入为空，请帮助我!!

3 个解决方案

#1

Using bcp, -k switch
使用bcp - k开关
Using BULK INSERT, use KEEPNULLS
使用批量插入，使用KEEPNULLS

After comment:

备注:后

Using SSIS "Bulk insert" task, options page, "Keep nulls" = true This is what the import wizard uses: but you'll have to save and edit it first because I see no option in my SSMS 2005 wizard.
使用SSIS“批量插入”任务，选项页，“保持null”= true这是导入向导使用的:但是您必须先保存并编辑它，因为我在SSMS 2005向导中没有看到任何选项。

#2

This can be set in the OLE DB Destination editor....there is a 'Keep nulls' option.

这可以设置在OLE DB目的地....编辑这里有一个“Keep nulls”选项。

#3

Alternative for those using the Import and Export Wizard on SQL Server Express, or anyone who finds themselves too lazy to modify the SSIS package:

对于使用SQL Server Express上的导入和导出向导的用户，或者发现自己懒得修改SSIS包的用户，可以选择:

Using text editing software before you run the wizard, replace NULLs with a valid value that you know doesn't appear in your dataset (eg. 987654; be sure to do a search first!) and then run the Import Export Wizard normally. If your data contains every single value (maybe bits or tinyints), you'll have some data massaging ahead of you, but it's still possible by using a temporary table with datatypes that can store a greater number of values. Once it's in SQL, use commands like

在运行向导之前，使用文本编辑软件，用您知道在数据集中没有出现的有效值替换NULLs。987654;确保先进行搜索!)然后正常运行导入导出向导。如果您的数据包含每个单独的值(可能是位或tinyints)，那么您将在前面获得一些数据信息，但是仍然可以使用带有数据类型的临时表来存储更多的值。在SQL中，使用命令。

UPDATE TempTable
SET Column1 = NULL
WHERE Column1 = 987654

to get those NULLs where they belong. If you've used a temporary table, use INSERT INTO or MERGE to get your data into your end table.

去得到它们所属的空值。如果您使用了临时表，请使用INSERT INTO或MERGE来将数据放入最终表。

#1

Using bcp, -k switch
使用bcp - k开关
Using BULK INSERT, use KEEPNULLS
使用批量插入，使用KEEPNULLS