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.
The output in the messages window will show you how you are getting on:
... COUNT 265000 COUNT 266000 COUNT 267000 COUNT 268000 COUNT 269000
SET @counter = 0;
SET @batchsize = 5000;
SET ROWCOUNT @batchsize;
WHILE @counter < (@numOfRecords / @batchsize) + 1
BEGIN
SET @counter = @counter + 1;
UPDATE dbo.Customer
SET
IsActiveDutyUsMilitary = 0
WHERE IsActiveDutyUsMilitary IS NULL;
END;
SET ROWCOUNT 0;
SET NOCOUNT ON;
DECLARE @rows INT, @count INT, @message VARCHAR(100);
SET @rows = 1;
SET @count = 0;
WHILE @rows > 0
BEGIN
BEGIN TRAN
SELECT TOP (5000) * from dbo.Customer
WHERE IsActiveDutyUsMilitary IS NULL
SET @rows = @@ROWCOUNT
SET @count = @count + @rows
RAISERROR('COUNT %d', 0, 1, @count) WITH NOWAIT
COMMIT TRAN
END