How to update large number of records in batch

If you ever try to update several millions of record in single Update statement then think several times because it can cause real pain.

Disadvantage of single Update statement is … Its considered as single transaction and it doesn’t commit all changes until its done so if you have large number of records to update then it may fill up your transaction log causing disk full error

Solution to this problem is run it in batches … using technique shown below. Key thing to notice is SET ROWCOUNT XXXX

Use Northwind

select * into #tmpCsutomer from dbo.Customers

SET NOCOUNT ON

declare  @totcounter integer
declare  @msg varchar(max)
declare @batchsize int

set @batchsize=10 --// <<<<<< batch size
set @totcounter = 0

SET ROWCOUNT @batchsize

DECLARE @rc int

WHILE (1=1)
BEGIN 
--LOOP START///////////////////////////

	update #tmpCsutomer
	set region = null
	where customerid in (select customerid from Customers)
	and region is not null

	set @rc=@@ROWCOUNT

	IF @rc != @batchsize
    BREAK

	SET @totcounter = @totcounter + @batchsize

	set @msg = 'Rows Processed...' + cast(@totcounter as varchar(20))
	print @msg
--LOOP END//////////////////////////
END

--PRINT LAST MSG 
SET @totcounter = @totcounter + @rc
set @msg = 'Rows Processed...' + cast(@totcounter as varchar(20))
print @msg

SET ROWCOUNT 0