Batching SQL Server Updates

We sometime have to apply updates to very large tables of data, it’s usually wise to do this in batches.

Here’s an example of an update being applied in 100,000 row batches – The raiserror command puts a message in the Messages window to tell us which batch has just been completed.


DECLARE @i INT = 0,
@j INT,
@msg VARCHAR(100)

SELECT @j = MAX(HouseholdKey) FROM DimHousehold

WHILE @i <= @j
BEGIN
BEGIN TRAN
UPDATE dh
SET dh.AvgDriveTime = AvgDeliveryTime
FROM DimHousehold dh
INNER JOIN #OrderData fo
ON dh.HouseholdKey = fo.HouseholdKey
AND dh.DeliveryType = ‘Delivery’
WHERE dh.HouseholdKey BETWEEN @i AND @i+99999
COMMIT TRAN

CHECKPOINT

SELECT @msg = CAST(@i AS VARCHAR(20)) + ‘ – ‘ + CAST(@i+99999 AS VARCHAR(20)) + ‘ household keys updated’
RAISERROR(@msg,1,1) WITH NOWAIT
SET @i = @i + 100000
END