Update millions of records in SQL Server

Posted: March 23, 2012 in Database

When updating millions of records in SQL Server, do it batch by batch, otherwise, it may take hours or days.

declare @startId int
declare @endId int
set @endId = 0

while( 1 = 1)
begin
     set @startId = 0

     select top 1 @startId = p.ProductID
     from Product p with(nolock)
     where p.ProductID > @endId
     order by p.ProductID asc

     if @startId  = 0
     begin
          break
     end

-- print @startId

     set rowcount 100000

     select @endId = p.ProductID
     from Product p with(nolock)
     where p.ProductID >= @startId
     order by p.ProductID asc

     set rowcount 0

-- print @endId

     BEGIN TRANSACTION

     update top (100000) p
     set p.dateUpdated = GetDate()
     from Product p with(rowlock)
     where p.ProductID >= @startId and p.ProductID <= @endId

     if @@ROWCOUNT = 0
     BEGIN
          COMMIT TRANSACTION
          BREAK
     END
     COMMIT TRANSACTION
-- 1 second delay

     WAITFOR DELAY '00:00:01';
end

I tried to execute this update statement on a table with 7 millions records, it takes less than 10 mins.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s