DEV Community


Answer: How to update large table with millions of rows in SQL Server?

boyukbas profile image baris Updated on ・1 min read
WHILE EXISTS (SELECT * FROM TableName WHERE Value <> 'abc1' AND Parameter1 = 'abc' AND Parameter2 = 123)
UPDATE TOP (1000) TableName
SET Value = 'abc1'
WHERE Parameter1 = 'abc' AND Parameter2 = 123 AND Value <> 'abc1'

I think it is a good practice.

Another answer by "Naomi N"

declare @Size int, @Loops int, @i int
set @Size = 100000
select @Loops = count(*)/@Size from Table1
set @I = 0

while @I <=@Loops
    ;with cte as (select ID, OldColumn,   NewColumn, case when  New_Column is null
                                then Replicate('0', 10 - Len(Old_Column))
                                     + Old_Column
                                else Replicate('0', 10 - Len(New_Column))
                                     + New_Column end as FixedColumn, 
    Row_Number() over (order by ID) as Row from Table1)

    update cte set NewColumn = FixedColumn
    where Row between @I* @Size and (@I+1)*@Size
    set @I = @I + 1

Discussion (0)

Editor guide