相同SQL Merge(Upsert)在SSMS正常但Python pyodbc执行行数异常

核心故障原因 #

90%以上概率是未关闭行计数返回导致批处理被驱动中断
pyodbc驱动的默认行为是将SQL执行过程中返回的每一条「X行受影响」的计数消息识别为独立结果集，不会自动跳过这些中间消息继续执行后续语句。你的代码用WHILE循环逐日期执行MERGE，每轮MERGE完成都会返回一条行计数消息，pyodbc在处理到第一批返回结果后就会终止剩余循环逻辑，这也是插入行数在69-72区间波动的核心原因——每次执行时数据库负载不同，驱动中断前跑完的循环次数不固定。
你在SSMS中执行时，客户端默认会自动消费所有中间计数消息直到批处理完全跑完，所以能得到预期的4302行结果。

最小修复方案 #

在你生成的SQL语句最顶部添加一行SET NOCOUNT ON;，关闭执行过程中的行计数返回即可：

def stage_to_dim_statement(now):
    return f"""
    SET NOCOUNT ON; -- 新增这一行即可解决循环中断问题
    DECLARE @dates table(id INT IDENTITY(1,1), date DATETIME)
    INSERT INTO @dates (date)
        SELECT DISTINCT TransactionDateTime FROM {cfg.stage_table} ORDER BY TransactionDateTime;

    DECLARE @i INT;
    DECLARE @cnt INT;
    DECLARE @date DATETIME;

    SELECT @i = MIN(id) - 1, @cnt = MAX(id) FROM @dates;
    WHILE @i < @cnt
    BEGIN
        SET @i = @i + 1
        SET @date = (SELECT date FROM @dates WHERE id = @i)
        MERGE {cfg.dim_customer} AS Target
        USING (SELECT * FROM {cfg.stage_table} WHERE TransactionDateTime = @date) AS Source 
            ON Target.CustomerCodeNK = Source.CustomerID
        WHEN MATCHED THEN
            UPDATE SET Target.AquiredDate = Source.AcquisitionDate, Target.AquiredSource = Source.AcquisitionSource,
        Target.ZipCode = Source.Zipcode, Target.LoadDate = CONVERT(DATETIME, '{now}'), Target.LoadSource = '{cfg.ingest_file_path}'
        WHEN NOT MATCHED THEN
            INSERT (CustomerCodeNK, AquiredDate, AquiredSource, ZipCode, LoadDate, LoadSource) VALUES (Source.CustomerID, 
        Source.AcquisitionDate, Source.AcquisitionSource, Source.Zipcode, CONVERT(DATETIME,'{now}'), '{cfg.ingest_file_path}');
    END
"""

其他必查项（避免后续踩坑） #

• 核对配置值：执行前先打印生成的完整SQL，确认cfg.stage_table、cfg.dim_customer的实际值和你在SSMS中测试用的表名完全一致，确认Python连接的数据库实例、库名和SSMS连接的一致，避免连错测试环境、写错表名导致结果不符。
• 统一时间格式：不要依赖SQL Server的隐式时间转换，把传入的now参数提前格式化为ISO8601标准格式（YYYY-MM-DD HH:MM:SS）再拼接进SQL，避免因为服务端区域设置和本地SSMS不一致，导致时间转换偏差、日期匹配错误。
• 优化冗余逻辑：现有逐日期循环的写法完全可以替换为单条MERGE语句，不需要遍历日期，性能更高也不会出现循环中断问题。注意源表需要按客户ID去重，避免同一个客户对应多条交易记录触发MERGE的多行匹配报错：

  SET NOCOUNT ON;
  MERGE {cfg.dim_customer} AS Target
  USING (
      SELECT 
          CustomerID,
          MAX(AcquisitionDate) AS AcquisitionDate,
          MAX(AcquisitionSource) AS AcquisitionSource,
          MAX(Zipcode) AS Zipcode
      FROM {cfg.stage_table}
      GROUP BY CustomerID
  ) AS Source 
  ON Target.CustomerCodeNK = Source.CustomerID
  WHEN MATCHED THEN
      UPDATE SET 
          Target.AquiredDate = Source.AcquisitionDate,
          Target.AquiredSource = Source.AcquisitionSource,
          Target.ZipCode = Source.Zipcode,
          Target.LoadDate = CONVERT(DATETIME, '{now_iso}'),
          Target.LoadSource = '{cfg.ingest_file_path}'
  WHEN NOT MATCHED THEN
      INSERT (CustomerCodeNK, AquiredDate, AquiredSource, ZipCode, LoadDate, LoadSource)
      VALUES (
          Source.CustomerID,
          Source.AcquisitionDate,
          Source.AcquisitionSource,
          Source.Zipcode,
          CONVERT(DATETIME, '{now_iso}'),
          '{cfg.ingest_file_path}'
      );
  

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