SQL Server中使用BULK INSERT插入NULL值的解决方案

作为日常用惯了PostgreSQL的人，我太懂你这种憋屈了——PostgreSQL处理文件里的NULL、NA这类标识转NULL简直顺滑，到SQL Server的BULK INSERT这儿就卡壳，尤其是面对几十上百个25GB级别的超大文件时，手动改文件或者靠字段留空根本不现实。下面给你几个实用的解决方案，都是我处理大体积数据集时亲测有效的：

方案1：使用格式化文件（Format File）映射空值标识 #

这是处理大文件最高效的方式，因为格式化文件可以精准指定每个字段如何识别空值，完全不需要修改源文件。

操作步骤： #

1. 先用bcp命令生成基础的XML格式模板文件：

   bcp YourDatabase.dbo.YourTable format nul -c -x -f YourFormatFile.xml -T
   

   说明：-c指定字符模式，-x生成XML格式文件，-T用Windows身份验证（如果是SQL账号验证，替换为-U 用户名 -P 密码）。

2. 编辑生成的XML文件，给需要识别空值的字段添加NULLIF节点，逐个指定要匹配的空值标识。比如要把NULL、NA、na、-、.都转成NULL：

   <COLUMN SOURCE="1" NAME="your_target_column" xsi:type="SQLVARCHAR">
     <NULLIF Value="NULL"/>
     <NULLIF Value="NA"/>
     <NULLIF Value="na"/>
     <NULLIF Value="-"/>
     <NULLIF Value="."/>
   </COLUMN>
   

   每个空值标识单独对应一个<NULLIF>节点即可。

3. 用BULK INSERT调用这个格式化文件完成导入：

   BULK INSERT YourDatabase.dbo.YourTable
   FROM 'D:\YourLargeDataFile.txt'
   WITH (
     FORMATFILE = 'D:\YourFormatFile.xml',
     FIELDTERMINATOR = ',', -- 根据你的文件分隔符调整（比如\t代表制表符）
     ROWTERMINATOR = '\n',
     TABLOCK, -- 大文件导入必加，减少日志开销提升性能
     ROWS_PER_BATCH = 100000 -- 按批次导入，避免内存溢出
   );
   

方案2：用OPENROWSET(BULK...)结合CASE语句处理 #

如果不想写格式化文件，也可以用OPENROWSET先读取文件内容，再通过CASE逻辑把特定标识转成NULL，适合临时处理或者字段数量不多的场景：

INSERT INTO YourDatabase.dbo.YourTable (col1, col2, col3)
SELECT
  CASE WHEN col1 IN ('NULL', 'NA', 'na', '-', '.') THEN NULL ELSE col1 END,
  CASE WHEN col2 IN ('NULL', 'NA', 'na', '-', '.') THEN NULL ELSE col2 END,
  -- 其他字段依次类推
FROM OPENROWSET(
  BULK 'D:\YourLargeDataFile.txt',
  FORMATFILE = 'D:\BasicFormatFile.xml' -- 用简单格式化文件指定字段顺序和类型
) AS BulkImportData;

这个方法灵活度高，但字段多的话写起来会繁琐，胜在不需要额外维护格式化文件。

大文件导入的性能优化提示 #

针对你这种25GB级别的数据集，导入时一定要注意：

• 把数据库切换为简单恢复模式，减少日志写入量
• 先禁用目标表的索引和约束，导入完成后再重建，能大幅提升速度
• 尽量在服务器本地读取文件，避免网络传输的性能损耗

当初我处理类似规模的数据集时，用格式化文件的方法比其他方式快了近30%，全程不需要碰源文件，非常省心。

内容的提问来源于stack exchange，提问作者elikesprogramming