MSSQL执行Bulk Insert时如何处理源数据中'NIL'形式的空值

原生 BULK INSERT 没有提供自定义空值占位符的配置项，只能识别CSV中真正的空字段为NULL，无法自动把文件里的'NIL'字符串转换成int类型可接受的NULL值，直接执行你贴的代码必然会抛出varchar转int失败的错误。

使用中间临时表承接原始数据，清洗转换后再入正式表，容错率最高，还可以顺便做脏数据校验，步骤如下：

1. 建和正式表对应的中间承接表，存原始值的列用字符串类型，长度足够容纳占位符和合法值即可
2. 把CSV全量数据先导入中间表，不会出现类型转换错误
3. 写转换逻辑，把'NIL'替换为NULL，合法值转成int后插入正式表

对应代码示例：

-- 1. 建正式表（沿用你原来的定义）
create table test (nid INT);

-- 2. 建临时中间表，用字符串类型承接原始值
CREATE TABLE #test_staging (nid_raw VARCHAR(10));

-- 3. 全量导入原始数据到中间表
bulk insert #test_staging from @FILEPATH with (format="CSV", firstrow=2);

-- 4. 清洗转换后插入正式表
INSERT INTO test(nid)
SELECT
    CASE WHEN nid_raw = 'NIL' THEN NULL
         WHEN ISNUMERIC(nid_raw) = 1 THEN CAST(nid_raw AS INT)
         -- 可在这里扩展脏数据处理逻辑，比如打印异常值、记录错误日志
         ELSE NULL END
FROM #test_staging;

-- 导入完成后清理临时表
DROP TABLE IF EXISTS #test_staging;

• 预处理CSV文件：如果允许提前修改源文件，可以把所有单独占一个字段的NIL替换为空值，替换后BULK INSERT会自动把空字段识别为NULL。注意这个方案有概率误替换字符串字段中合法存在的NIL内容，大文件替换效率也偏低，只适合小文件且确认NIL仅作为空值占位符的场景。
• 自定义格式文件方案：可以为BULK INSERT编写专用格式文件配置字段转换规则，但配置复杂度高，远不如中间表方案易维护，不推荐使用。

不要试图通过给int字段加默认值、添加KEEPNULLS参数解决问题，这些配置都无法让BULK INSERT自动识别自定义的字符串空值占位符。

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