SQL While循环重复打印同一ID问题排查与修正方案

我的结果集包含2958行数据，尝试通过While循环遍历该结果集并为每行分配新ID，但发现每个新ID都被重复打印2958次。请问我哪里出错了？该如何修正？待查询逻辑正确后，我将把它用于Insert操作。

相关代码如下：

DECLARE @ID AS INT
SET @ID = 16342 -- Next Available ID
DECLARE @RowCount INT
SET @RowCount = (SELECT COUNT(ID) FROM [dbo].[Table1] WHERE ID2 is null)
DECLARE @I INT = 1


WHILE (@I <= @RowCount)
BEGIN
    SET IDENTITY_INSERT [Table1] ON
--INSERT INTO [dbo].[Table1] 
--(@ID, Code, Description1, Description2,Field1,Field2,Field3,ID2)

    PRINT @ID

    ((SELECT @ID, Code, Description1, Description2,Field1,Field2,Field3,15 as ID2
        FROM [dbo].[Table1] 
        WHERE ID2 is null))
    
    SET @ID = @ID + 1
    SET @I = @I +1
    SET IDENTITY_INSERT [Table1] OFF
    
END

• 循环内SELECT返回全量数据：每次循环执行SELECT ... WHERE ID2 is null时，都会返回所有2958行符合条件的数据，当前的@ID会被赋值给这所有行，导致每个@ID对应2958次输出。
• IDENTITY_INSERT开关滥用：每次循环重复开启、关闭IDENTITY_INSERT完全没必要，会额外消耗性能。
• 逐行循环不符合SQL特性：SQL是为集合操作设计的，逐行循环不仅效率低下，还容易出现逻辑疏漏。

方案一：集合操作一次性处理（推荐） #

利用窗口函数生成连续新ID，一次性完成所有行的处理，效率远高于循环：

插入新行场景 #

SET IDENTITY_INSERT [dbo].[Table1] ON;

INSERT INTO [dbo].[Table1] (ID, Code, Description1, Description2, Field1, Field2, Field3, ID2)
SELECT 
    16342 + ROW_NUMBER() OVER (ORDER BY ID) AS NewID, -- 生成连续递增的新ID
    Code, 
    Description1, 
    Description2, 
    Field1, 
    Field2, 
    Field3, 
    15 AS ID2
FROM [dbo].[Table1] 
WHERE ID2 IS NULL;

SET IDENTITY_INSERT [dbo].[Table1] OFF;

更新现有行ID场景（不建议手动修改标识列，仅作参考） #

SET IDENTITY_INSERT [dbo].[Table1] ON;

WITH CTE AS (
    SELECT 
        ID,
        16342 + ROW_NUMBER() OVER (ORDER BY ID) AS NewID
    FROM [dbo].[Table1] 
    WHERE ID2 IS NULL
)
UPDATE CTE
SET ID = NewID;

SET IDENTITY_INSERT [dbo].[Table1] OFF;

方案二：循环逐行处理（不推荐） #

如果必须用循环，需保证每次只处理一行数据，避免重复选取：

DECLARE @ID AS INT = 16342;
-- 根据实际字段类型调整变量类型
DECLARE @Code VARCHAR(50);
DECLARE @Description1 VARCHAR(100);
DECLARE @Description2 VARCHAR(100);
DECLARE @Field1 INT;
DECLARE @Field2 VARCHAR(50);
DECLARE @Field3 VARCHAR(50);

SET IDENTITY_INSERT [dbo].[Table1] ON;

WHILE EXISTS (SELECT 1 FROM [dbo].[Table1] WHERE ID2 IS NULL)
BEGIN
    -- 取出一行未处理的数据
    SELECT TOP 1
        @Code = Code,
        @Description1 = Description1,
        @Description2 = Description2,
        @Field1 = Field1,
        @Field2 = Field2,
        @Field3 = Field3
    FROM [dbo].[Table1] 
    WHERE ID2 IS NULL
    ORDER BY ID; -- 按ID排序保证处理顺序

    -- 打印并插入数据
    PRINT @ID;
    INSERT INTO [dbo].[Table1] (ID, Code, Description1, Description2, Field1, Field2, Field3, ID2)
    VALUES (@ID, @Code, @Description1, @Description2, @Field1, @Field2, @Field3, 15);

    SET @ID = @ID + 1;

    -- 若需标记该行已处理（避免重复取数），可添加UPDATE语句
    -- UPDATE [dbo].[Table1] SET ID2 = 15 WHERE Code = @Code AND Description1 = @Description1;
END

SET IDENTITY_INSERT [dbo].[Table1] OFF;

• 优先选择方案一的集合操作，SQL对集合处理的效率远高于逐行循环，数据量越大优势越明显。
• 若使用循环，必须确保每次只处理一行，且避免重复选取同一行数据。

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