Home › SQL Server › How to update large number of records in batch

How to update large number of records in batch

Posted on Posted in SQL Server, T-SQL Tips No Comments Tagged with ,
How to update large number of records in batch
T SQL Date format convert function

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

Related Posts

About

Binary World is a Software Development company located in Atlanta, USA (since 2007). Binary World specialized in Business Intelligence, mobile, cloud computing and .Net Application Development.

Leave a Reply