SQL Server Bulk Insert插入bigint到int列未报错却生成错误值问题

问题原因 #

SQL Server的BULK INSERT在将字符格式的大数值转换为int类型时，遵循以下规则：

1. int是32位有符号整数，合法范围为 -2147483648 ~ 2147483647。
2. 当导入的十进制数值超出int范围时，SQL Server会对该数值执行模2^32（即4294967296）运算，取余数作为转换结果。
3. 如果余数落在int的合法范围内，就会静默插入该截断后的值，不触发错误；如果余数超出int的有符号范围（比如余数大于2147483647或小于-2147483648），才会抛出Msg 4867的溢出错误。

具体验证 #

• 对于数值310067463717：

  310067463717 % 4294967296 = 829818405
  

  该余数在int范围内，因此被静默插入。
• 对于数值2147483648：

  2147483648 % 4294967296 = 2147483648
  

  该余数大于int的最大值2147483647，触发溢出报错。

数据清理与修复方案 #

1. 定位错误数据 #

如果原始数据源（如CSV、上游系统）还保留着正确的bigint数值，可以通过以下逻辑定位表中被截断的错误数据：

-- 假设源数据存储在临时表#source中，包含正确的bigint值id
SELECT t.t AS 错误值, s.id AS 正确值
FROM #t t
JOIN #source s ON t.t = (s.id % 4294967296)
WHERE s.id > 2147483647 OR s.id < -2147483648

2. 修复错误数据 #

先将目标列修改为bigint，再从原始数据源获取正确值覆盖错误数据：

-- 先修改列类型为bigint
ALTER TABLE #t ALTER COLUMN t bigint;

-- 用正确值更新错误数据
UPDATE t
SET t.t = s.id
FROM #t t
JOIN #source s ON t.t = (s.id % 4294967296)
WHERE s.id > 2147483647 OR s.id < -2147483648

3. 修复ETL流程 #

• 确认目标表列类型已更新为bigint，与源列保持一致。
• 在BULK INSERT语句中添加CHECK_CONSTRAINTS选项，强制开启约束检查，避免后续再次出现静默截断：

  BULK INSERT #t 
  FROM 'c:\temp\test.csv'
  WITH (
      DATAFILETYPE = 'char',
      FIELDTERMINATOR = '|',
      CHECK_CONSTRAINTS -- 开启约束检查，超出范围直接报错
  )
  

4. 预防措施 #

• 修改表结构时，同步更新所有依赖的ETL、报表等流程，确保数据类型匹配。
• 批量导入场景优先使用与源数据匹配的数值类型（如bigint），避免不必要的类型转换风险。

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