Update all rows but with limited number at a time

Question

First of all, sorry if this question has been answered before. I am new to Microsoft SQL and StackExchange and I wasn't able to find help regarding this thing I want to improve.

So I have one task to update all rows in a table with data from another row. This table has currently 3,9 million rows and counting. I want to update all rows in the table but only 200k-300k rows at a time.

My current script looks like this:

alter table TableA add blob_image_tmp varbinary ( max ) 
GO

update TableA
 set blob_image_tmp = cast ( blob_image as varbinary ( max ) )
 where columnA < 200000
GO

update TableA 
 set blob_image_tmp = cast ( blob_image as varbinary ( max ) )
 where columnA > 199999 and columnA < 400000
GO

update TableA
 set blob_image_tmp = cast ( blob_image as varbinary ( max ) )
 where columnA > 399999 and columnA < 600000
GO

....and so on up to 3,9million.

Then I end it with the following code:

alter table TableA drop column blob_image
GO

exec sp_rename 'TableA.blob_image_tmp' , 'blob_image' , 'COLUMN' 
GO

alter table TableA rebuild

Is there a simpler way to achieve the same result? I have another table I need to do the same thing with but that one has more than 10 million rows so it feels annoying to split each part up like this.

Answer 1

I would set up a WHILE loop similar to the following example:

set nocount on
IF OBJECT_ID('tempdb..#TableToUpdate') IS NOT NULL
    drop table #TableToUpdate
create table #TableToUpdate (KeyColumn int IDENTITY(1,1) NOT NULL,OriginalColumn int, NewColumn int)
go
insert into #TableToUpdate(OriginalColumn,NewColumn) values(1,1)
go 105

declare @RowsInTable int
declare @BeginRange int = 0
Declare @EndRange int = 0
Declare @Increment int = 10
set @RowsInTable=(select count(*) from #TableToUpdate)

while @EndRange < @RowsInTable
begin

set @EndRange += @Increment

print 'updating ranges from ' + convert(varchar(100),@BeginRange) + ' to ' + convert(varchar(100),@EndRange)

update #TableToUpdate
 set NewColumn = OriginalColumn
 where KeyColumn between @BeginRange and @EndRange

set @BeginRange = @EndRange

end
GO