SQL Bulk Update in Batches
When you perform a SQL bulk update, you can just press go and wait. Most humans, though, get an increasing feeling of impending doom when the clock ticks up towards several hours with no visible progress.
If you are running a transaction and have other things competing for the table, you’ll start hearing about slow downs as everyone competes for the rows.
You can solve this with the following SQL bulk update script. This script updates in small transaction batches of 1000 rows at a time. You can use the general idea for any bulk update as long as you are okay with having the change committed in batches, and possibly being partially applied.
You can also apply the pattern of update shown in this example to make the change continue from where it left off if you have to stop it at any point, such as when you lose a connection.
SET NOCOUNT ON;
DECLARE @rows INT, @count INT, @message VARCHAR(100);
SET @rows = 1;
SET @count = 0;
WHILE @rows > 0
BEGIN
BEGIN TRAN
UPDATE TOP (1000) MyBigTable
SET MyColumn = MyColumn + 42
WHERE MyColumn < 10
SET @rows = @@ROWCOUNT
SET @count = @count + @rows
RAISERROR('COUNT %d', 0, 1, @count) WITH NOWAIT
COMMIT TRAN
END
The output in the messages window will show you how you are getting on:
... COUNT 265000 COUNT 266000 COUNT 267000 COUNT 268000 COUNT 269000
Countdown
If you prefer a countdown to zero, you might prefer this version.
SET NOCOUNT ON;
DECLARE @rows INT, @count INT, @message VARCHAR(100);
SET @rows = 1;
SET @count = 0;
WHILE @rows > 0
BEGIN
BEGIN TRAN
UPDATE TOP (1000) MyBigTable
SET MyColumn = MyColumn + 42
WHERE MyColumn < 10
SET @rows = @@ROWCOUNT
SELECT @count = COUNT(1) FROM MyBigTable WHERE MyColumn < 10
RAISERROR('COUNT %d', 0, 1, @count) WITH NOWAIT
COMMIT TRAN
END
... COUNT 4400 COUNT 4300 COUNT 4200 COUNT 4100 COUNT 4000
