Bulk UPDATE of a large table

--Shane Devane
--August 2005
--http://practicalsqlserver.blogspot.com

--BUSINESS TASK
--reset product boolean value to allow for 
--regeneration

--CODE PURPOSE
--to update a column(s) value to a set value 
--within all rows in a large table without 
--causing locking issues

--SOLUTION
--to store all row IDs that need to be changed
--from large table into a temporary table 
--(with index) and then loop through and update
--rows in bulk groups ie. using TOP statements
--to define maximum rows to update.

--STEPPING THROUGH
--1. get all row IDs that need to be updated and 
--save into temporary table
--2. loop through temporary table
--3. get the TOP 100 etc. IDs from the temporary 
--table and perform the UPDATE
--4. update the temporary and mark row IDs as 
--being updated eg. "done"

--USAGE
--you will need to adjust a few lines of code 
--below
--(1) your main query to retrive the row IDs from 
--your large table
--(2) the UPDATE command you wish to perform on 
--each row(s)
--(3) adjust to two sub query commands TOP value 
--to allow for bulk updates

USE Northwind

--initialise variables
DECLARE @count INT

--create ID container table with index
IF (object_id('tempdb..#BulkUpdate_08_2005') 
        IS NOT NULL)
        BEGIN
        DROP TABLE "#BulkUpdate_08_2005"
        END
CREATE TABLE #BulkUpdate_08_2005 (
rowID INT,
done BIT DEFAULT 0
) 
CREATE  CLUSTERED INDEX #IndexBulkUpdate_08_2005
ON #BulkUpdate_08_2005 (rowID)

--(1) populate temporary table
--[user needs to edit]
INSERT #BulkUpdate_08_2005 (rowID) 
        SELECT DISTINCT EmployeeID
        FROM Employees
        WHERE Country = 'USA'

--main loop 
SELECT  @count = COUNT(*) 
        FROM #BulkUpdate_08_2005
        WHERE done = 0

WHILE (@count > 0) 
        BEGIN

        --(2) perform update for rows
        --[user needs to edit]
        UPDATE Employees
        SET Country = 'United States' 
        WHERE EmployeeID IN     
                        (
                        SELECT TOP 1 rowID 
                        FROM #BulkUpdate_08_2005
                        WHERE done = 0
                        ORDER BY rowID DESC 
                        )

        --(3) update counters and temporary table
        --[user needs to edit]
        UPDATE #BulkUpdate_08_2005
        SET done = 1 WHERE rowID IN     
                        (
                        SELECT TOP 1 rowID 
                        FROM #BulkUpdate_08_2005
                        WHERE done = 0
                        ORDER BY rowID DESC 
                        )
   
        SELECT @count = COUNT(*) 
        FROM #BulkUpdate_08_2005
        WHERE done = 0

        --pause 30 seconds to let DB recoupe
        WAITFOR DELAY '000:00:30'
        END