SQL Server存储过程While循环无法终止，重复上传数据求助

核心问题根源 #

• 过滤阈值未更新：每次循环执行时，@THRESHOLD变量始终保持初始值，导致每次查询都拉取相同时间范围的数据，永远无法取完所有增量数据，循环无法终止。
• 重复数据产生：由于每次查询的过滤条件不变，同一批数据会被反复抽取插入，导致目标表出现大量重复记录。

修复步骤 #

1. 跟踪批次最大更新时间：在每次批量抽取时，记录当前批次数据的最大modifiedDate，作为下一次循环的过滤阈值。
2. 动态更新阈值变量：每完成一批数据插入后，将@THRESHOLD更新为当前批次的最大modifiedDate，确保下一批只拉取更新的增量数据。
3. 保留循环终止逻辑：当某批次拉取的行数小于设定的@BATCH_COUNTER时，说明已无更多增量数据，触发终止条件退出循环。

修复后的代码示例 #

WHILE 1=1
BEGIN
    SET @BATCH_COUNTER = 100000;  
    -- 声明变量存储当前批次的最大modifiedDate
    DECLARE @MAX_MODIFIED_DATE DATETIME;

    -- 调整查询逻辑：先将数据存入临时表，同时获取批次最大更新时间
    SET @QUERY = '
    DECLARE @TempBatch TABLE (
        Id_company VARCHAR(50),
        Id INT,
        Code VARCHAR(50),
        StartDate DATETIME,
        EndDate DATETIME,
        modifiedDate DATETIME
    );

    -- 从链接服务器拉取增量数据到临时表
    INSERT INTO @TempBatch
    SELECT 
        ''''' + @COUNTRY_CODE + ''''' as Id_company,
        Id,
        Code,
        StartDate,
        EndDate,
        modifiedDate
    FROM OPENQUERY ([' + @LINKED_SERVER + '],''
        SELECT TOP ' + CAST(@BATCH_COUNTER as NVARCHAR(50)) + ' 
            ''''' + @COUNTRY_CODE + ''''' as Id_company,
            Id,
            Code,
            StartDate,
            EndDate,
            modifiedDate
        FROM table
        WHERE  createdDate <= ''''' + @END_DATE + '''''
        AND modifiedDate > ''''' + @THRESHOLD + '''''
        ORDER BY modifiedDate
    '');

    -- 插入目标表
    ' + @INSERT + '
    SELECT 
        Id,
        Code,
        StartDate,
        EndDate
    FROM @TempBatch' + @INSERT_INTO + ';

    -- 获取当前批次的最大modifiedDate
    SELECT @MAX_MODIFIED_DATE = MAX(modifiedDate) FROM @TempBatch;';

    SET @count = 0
reexecute:
 
    BEGIN TRANSACTION @table_name;
    BEGIN TRY
        EXEC(@QUERY);

        SET @ROWCOUNT = @@ROWCOUNT
        SET @ROWCOUNT_GERAL = @ROWCOUNT_GERAL + @ROWCOUNT

        -- 更新阈值为当前批次的最大更新时间
        IF @MAX_MODIFIED_DATE IS NOT NULL
            SET @THRESHOLD = @MAX_MODIFIED_DATE;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRANSACTION @table_name;
        END

        IF @count < 3
        BEGIN
            SET @count = @count + 1
            WAITFOR DELAY '00:01:00';
            GOTO reexecute
        END

        SET @PRINT_MSG = ERROR_MESSAGE()
        RAISERROR(@PRINT_MSG,16,1) WITH NOWAIT
        BREAK
        RETURN;
    END CATCH;

    IF @@TRANCOUNT > 0
    BEGIN
        COMMIT TRANSACTION @table_name;
    END

    -- 批次数据量不足时退出循环
    IF @ROWCOUNT < @BATCH_COUNTER 
        BREAK;

    SET @PRINT_MSG = '已加载批次数据量: ' + CAST(@ROWCOUNT AS VARCHAR(150))
    RAISERROR(@PRINT_MSG,0,1) WITH NOWAIT
END

额外优化建议 #

• 简化动态SQL拼接：原代码中多重嵌套引号易出错，可使用STRING_ESCAPE或参数化方式优化字符串拼接逻辑。
• 添加唯一约束：在目标表针对Id + Id_company添加唯一键约束，即使逻辑出现问题，也能直接拦截重复数据插入。
• 完善日志记录：新增日志表记录每次批次的抽取时间、处理行数、阈值变化等信息，方便后续问题排查。

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