SQL Server中WHILE循环遍历临时表时INSERT语句仅抓取第一条Code的技术问题排查

你的代码里每次只能插入第一条Code的核心原因是：在内部的WHILE循环中，你用SET ROWCOUNT 1限制了查询返回1行，但每次循环都从STRL_Training_Active中取同一个ID的第一条记录，没有跳过已经插入的行。所以不管循环多少次，插入的都是同一个Code。

另外，SET ROWCOUNT在SQL Server 2012及以后版本已经被官方弃用，建议用TOP (n)或者基于行号的方式替代。

我推荐两种方式，优先用集合操作（SQL最擅长的方式，效率远高于循环），如果业务必须用循环，再用优化后的循环版本。

方式1：用集合操作批量插入（推荐） #

完全去掉嵌套循环，用ROW_NUMBER()来自动分配Seq值，一次性处理所有ID：

WITH ID_List AS (
    SELECT DISTINCT ID FROM STX_JJ_Keller_Training
),
Training_With_RowNum AS (
    SELECT 
        t.ID,
        t.Code,
        t.Completed,
        t.[Enrollment Date],
        -- 为每个ID的Training记录分配行号，作为Seq的基础
        ROW_NUMBER() OVER (PARTITION BY t.ID ORDER BY (SELECT NULL)) AS RowNum
    FROM STRL_Training_Active t
    JOIN ID_List i ON t.ID = i.ID
),
Existing_Max_Seq AS (
    SELECT 
        HRRef AS ID,
        MAX(Seq) AS MaxSeq
    FROM HRET_10_21
    WHERE HRCo = 2
    GROUP BY HRRef
)
INSERT INTO HRET_10_21 (
    HRCo,HRRef,Seq,TrainCode,[Status],[Date],CompleteDate, 
    [Type],DegreeYN,Cost,ReimbursedYN,Instructor1099YN, 
    VendorGroup,OSHAYN,MSHAYN,FirstAidYN,CPRYN,WorkRelatedYN
)
SELECT 
    2 AS HRCo,
    twr.ID AS HRRef,
    -- 存在已有记录则用MaxSeq+RowNum，否则直接用RowNum
    ISNULL(ems.MaxSeq, 0) + twr.RowNum AS Seq,
    twr.Code AS TrainCode,
    CASE WHEN twr.Completed IS NOT NULL THEN 'C' ELSE 'S' END AS Status,
    twr.[Enrollment Date] AS [Date],
    twr.Completed AS CompleteDate,
    'T' AS [Type],
    'N' AS DegreeYN,
    0 AS Cost,
    'N' AS ReimbursedYN,
    'N' AS Instructor1099YN,
    1 AS VendorGroup,
    'N' AS OSHAYN,
    'N' AS MSHAYN,
    'N' AS FirstAidYN,
    'N' AS CPRYN,
    'Y' AS WorkRelatedYN
FROM Training_With_RowNum twr
LEFT JOIN Existing_Max_Seq ems ON twr.ID = ems.ID;

方式2：优化循环版本（保留原有逻辑） #

如果必须用循环，需要为每个ID的Training记录创建临时存储，逐行处理：

DECLARE @ID int
DECLARE @NumOfSeq int
DECLARE @Code nvarchar(250)
DECLARE @CurrentSeq int

-- 用临时表存储待处理的ID
CREATE TABLE #ID_TempTable (ID int PRIMARY KEY)
INSERT INTO #ID_TempTable SELECT DISTINCT ID FROM STX_JJ_Keller_Training

WHILE EXISTS(SELECT 1 FROM #ID_TempTable)
BEGIN
    -- 取出一个ID处理
    SELECT TOP 1 @ID = ID FROM #ID_TempTable
    PRINT 'ID equals ' + CAST(@ID AS varchar)
    DELETE FROM #ID_TempTable WHERE ID = @ID

    -- 为当前ID的所有Training记录创建临时表，带行号
    CREATE TABLE #Training_Temp (
        RowID INT IDENTITY(1,1) PRIMARY KEY,
        Code nvarchar(250),
        Completed DATE,
        EnrollmentDate DATE
    )
    INSERT INTO #Training_Temp (Code, Completed, EnrollmentDate)
    SELECT Code, Completed, [Enrollment Date]
    FROM STRL_Training_Active WHERE ID = @ID

    SET @NumOfSeq = @@ROWCOUNT
    IF @NumOfSeq = 0
    BEGIN
        DROP TABLE #Training_Temp
        CONTINUE
    END

    DECLARE @cnt INT = 1
    DECLARE @maxSeq INT = 0

    -- 获取已有的最大Seq（如果存在）
    IF EXISTS(SELECT 1 FROM HRET_10_21 WHERE HRCo = 2 AND HRRef = @ID)
    BEGIN
        SELECT @maxSeq = MAX(Seq) FROM HRET_10_21 WHERE HRCo = 2 AND HRRef = @ID
    END

    WHILE @cnt <= @NumOfSeq
    BEGIN
        -- 从临时表中取出当前行的Code
        SELECT 
            @Code = Code
        FROM #Training_Temp WHERE RowID = @cnt

        BEGIN TRANSACTION
        INSERT INTO HRET_10_21 (
            HRCo,HRRef,Seq,TrainCode,[Status],[Date],CompleteDate, 
            [Type],DegreeYN,Cost,ReimbursedYN,Instructor1099YN, 
            VendorGroup,OSHAYN,MSHAYN,FirstAidYN,CPRYN,WorkRelatedYN
        )
        VALUES (
            2, @ID, @maxSeq + @cnt, @Code,
            CASE WHEN (SELECT Completed FROM #Training_Temp WHERE RowID = @cnt) IS NOT NULL THEN 'C' ELSE 'S' END,
            (SELECT EnrollmentDate FROM #Training_Temp WHERE RowID = @cnt),
            (SELECT Completed FROM #Training_Temp WHERE RowID = @cnt),
            'T', 'N', 0, 'N', 'N', 1, 'N', 'N', 'N', 'N', 'Y'
        )
        COMMIT

        SET @cnt = @cnt + 1
    END

    DROP TABLE #Training_Temp
END

DROP TABLE #ID_TempTable

1. 集合操作版本：利用ROW_NUMBER()为每个ID的Training记录分配唯一序号，结合已有MaxSeq计算新的Seq，一次性完成插入，避免循环的性能损耗。
2. 循环优化版本：为每个ID创建临时存储所有Training记录，通过IDENTITY列生成行号，逐行取出不同的Code插入，确保每条Code都被处理。
3. 替换了弃用的SET ROWCOUNT，改用TOP (1)和行号定位，符合SQL Server的最佳实践。

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