--创建待使用的表格
CREATE TABLE Orders
(
OrderID INT ,
CostValue DECIMAL(18, 2)
);
WITH cte_temp
AS ( SELECT 1 AS OrderID
UNION ALL
SELECT OrderID + 1
FROM cte_temp
WHERE OrderID < 10000
)
INSERT INTO Orders
( OrderID
)
SELECT OrderID
FROM cte_temp
OPTION ( MAXRECURSION 32767 );
SELECT *
FROM dbo.Orders;
--UPDATE dbo.Orders SET CostValue=NULL
--用游标的方式填充数据 51s
GO
DECLARE @OrderID INT;
DECLARE cursor_CostValue CURSOR
FOR
SELECT OrderID
FROM dbo.Orders;
OPEN cursor_CostValue;
FETCH NEXT FROM cursor_CostValue INTO @OrderID;
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE dbo.Orders
SET CostValue = @OrderID + 100
WHERE OrderID = @OrderID;
FETCH NEXT FROM cursor_CostValue INTO @OrderID;
END;
CLOSE cursor_CostValue;
DEALLOCATE cursor_CostValue;
--使用while循环的方式 用时13s
GO
DECLARE @RowID INT;
--获取待处理的数据记录到临时表
--字段说明 RowID 记录的行号 DealFlg 行处理标识
SELECT RowID = IDENTITY( INT,1,1 ),
DealFlg = 0 ,
OrderID ,
CostValue = 0
INTO #Temp
FROM dbo.Orders;
SELECT @RowID = MIN(RowID)
FROM #Temp
WHERE DealFlg = 0;
--如果最小行号不为空 则表示有数据
WHILE @RowID IS NOT NULL
BEGIN
UPDATE #Temp
SET DealFlg = 1 ,
CostValue = OrderID + 100
WHERE RowID = @RowID;
SELECT @RowID = MIN(RowID)
FROM #Temp
WHERE DealFlg = 0;
END;
--此时 临时表中的costvalue已经被处理完毕
UPDATE a
SET a.CostValue = b.CostValue
FROM dbo.Orders a
JOIN #Temp b ON a.OrderID = b.OrderID;