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

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

Mar 9 '16

18

WHILE EXISTS (SELECT * FROM TableName WHERE Value <> 'abc1' AND Parameter1 = 'abc' AND Parameter2 = 123)
BEGIN
UPDATE TOP (1000) TableName
SET Value = 'abc1'
WHERE Parameter1 = 'abc' AND Parameter2 = 123 AND Value <> 'abc1'
END

Open Full Answer

I think it is a good practice.

Another answer by "Naomi N"
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/0ce6593d-9588-477a-a4cd-ce64f999eb52/update-700-million-records-quickly?forum=transactsql

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

while @I <=@Loops
  begin
    ;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
end