为何SQL Server转换数据无报错，插入同数据却报varchar转int错误？

出现这种差异的核心原因是SQL Server查询优化器针对SELECT和INSERT操作生成的执行计划不同，导致数据转换的执行顺序或数据扫描范围发生变化，具体分以下几种情况：

• 执行顺序颠倒：
  SELECT DISTINCT时，优化器可能先对源表的f.ID和i.ID（varchar类型）执行DISTINCT去重，再对去重后的有效varchar值执行CONVERT(int)转换。而INSERT操作中，优化器可能调整了执行顺序，先尝试对所有行的ID值执行转换，再做DISTINCT去重。如果源数据中存在无法转换为int的varchar值（比如包含非数字字符、长度超出int范围），就会触发转换错误。

• 数据扫描范围不同：
  执行SELECT时，若源表有合适的索引，优化器可能通过索引扫描获取数据，而索引中只包含有效的可转换ID；但INSERT操作可能需要扫描基表的所有行，从而碰到索引中未包含的无效ID值，导致转换失败。另外，如果SELECT时客户端只读取了部分结果（比如只查看前N行），未扫描到包含无效值的行，也会出现SELECT无报错但INSERT报错的情况。

• 查询优化器的成本估算差异：
  优化器会根据数据量、统计信息等因素选择成本最低的执行计划。SELECT和INSERT的目标不同（一个是返回结果集，一个是写入数据），优化器可能选择不同的执行路径，导致转换操作的时机不同，进而触发错误。

• 定位无效数据：
  可以通过以下语句找出源表中无法转换为int的ID值：

  -- 检查f表中无法转换为int的ID
  SELECT f.ID
  FROM XY...
  WHERE TRY_CONVERT(int, f.ID) IS NULL
  
  -- 检查i表中无法转换为int的ID
  SELECT i.ID
  FROM XY...
  WHERE TRY_CONVERT(int, i.ID) IS NULL
  

• 强制过滤无效数据：
  通过WHERE子句先过滤掉无法转换的行，再执行转换和插入，确保只处理有效数据：

  INSERT INTO dbo.test (GroupID, ItemID, ModifiedDate, ModifiedBy)
  SELECT DISTINCT 
      CONVERT(int, f.ID) AS GroupID, 
      CONVERT(int, i.ID) AS ItemID,
      GETDATE() AS ModifiedDate,
      N'StageSyncImp' AS ModifiedBy
  FROM 
      XY...
  WHERE 
      TRY_CONVERT(int, f.ID) IS NOT NULL
      AND TRY_CONVERT(int, i.ID) IS NOT NULL
  

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