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
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
Top comments (0)