如何编写WHILE循环批量处理重复交易ID的INSERT与DELETE操作

问题场景 #

需要处理一批重复的Transaction ID，先将对应订单记录插入Customer_Deleted_OrderRecordes作为日志，再从OrdersTable中删除这些记录。当前SQL只能处理单行数据，需要实现批量处理。

最优方案：无需WHILE循环的批量操作 #

SQL原生支持批量INSERT/DELETE，效率远高于逐行循环。直接通过关联查询完成日志插入，再删除目标记录即可，代码如下：

-- 先批量插入日志记录
INSERT INTO Customer_Deleted_OrderRecordes ([Date], [CustomerName], [ItemID], [TransactionID], [Invoice])
SELECT 
    O.date,
    c.FullName,
    O.ItemID,
    O.TransactionID,
    O.Invoice 
FROM
    CustomerTable c
INNER JOIN
    OrdersTable O ON c.ID = O.CustomerId
WHERE 
    O.TransactionID IN (1680339,1680340,1680341,1680342,1680343,1680344,1680345,1680346,1680347,1680348,1680349,1680350,1680351,1680352,1680353,1680354,1680355,1680356,1680357,1680358,1680359)

-- 再批量删除原订单记录
DELETE FROM OrdersTable 
WHERE TransactionID IN (1680339,1680340,1680341,1680342,1680343,1680344,1680345,1680346,1680347,1680348,1680349,1680350,1680351,1680352,1680353,1680354,1680355,1680356,1680357,1680358,1680359)

说明 #

• 修正了原代码中@CustomerName = c.FullName=的语法错误（多余的等号）
• 修正了日志表名Customer_Deleted__OrderRecordes的多余下划线（如果实际表名确实是两个下划线，可忽略此修改）
• 批量操作避免了逐行循环的性能损耗，适合处理大量数据

需求特定：WHILE循环逐行处理方案 #

如果必须使用WHILE循环实现逐行处理，可通过临时表存储需要处理的TransactionID，再循环执行操作：

-- 创建临时表存储需要处理的TransactionID
CREATE TABLE #TempTransactionIDs (TransactionID INT)
INSERT INTO #TempTransactionIDs (TransactionID)
VALUES 
(1680339),(1680340),(1680341),(1680342),(1680343),(1680344),(1680345),(1680346),(1680347),(1680348),(1680349),(1680350),(1680351),(1680352),(1680353),(1680354),(1680355),(1680356),(1680357),(1680358),(1680359)

-- 声明变量
DECLARE @Date Datetime
DECLARE @CustomerName nvarchar(90)
DECLARE @ItemID int 
DECLARE @CurrentTransactionID int
DECLARE @Invoice int

-- 取第一个需要处理的TransactionID
SELECT TOP 1 @CurrentTransactionID = TransactionID FROM #TempTransactionIDs

WHILE @CurrentTransactionID IS NOT NULL
BEGIN
    -- 查询当前TransactionID对应的记录
    SELECT 
        @Date = O.date,
        @CustomerName = c.FullName,
        @ItemID = O.ItemID,
        @Invoice = O.Invoice 
    FROM
        CustomerTable c
    INNER JOIN
        OrdersTable O ON c.ID = O.CustomerId
    WHERE 
        O.TransactionID = @CurrentTransactionID

    -- 插入日志
    INSERT INTO Customer_Deleted_OrderRecordes ([Date], [CustomerName], [ItemID], [TransactionID], [Invoice])
    VALUES (@Date, @CustomerName, @ItemID, @CurrentTransactionID, @Invoice)

    -- 删除原记录
    DELETE FROM OrdersTable 
    WHERE TransactionID = @CurrentTransactionID

    -- 移除已处理的ID，取下一个待处理ID
    DELETE FROM #TempTransactionIDs WHERE TransactionID = @CurrentTransactionID
    SELECT TOP 1 @CurrentTransactionID = TransactionID FROM #TempTransactionIDs
END

-- 删除临时表
DROP TABLE #TempTransactionIDs

说明 #

• 使用临时表存储待处理的TransactionID，避免重复写IN列表
• 循环中逐行处理每个ID，完成日志插入和原记录删除
• 处理完成后记得删除临时表，避免占用资源

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