I already spent some time trying to figure this out, but I am still somewhat stuck an I can't really find the solution online as I think I am missing the keywords.
I want to update an SQL tables in batches, meaning I have a few million entries and want to update index 0-999, 1000-1999 step by step to avoid a huge database lock.
This is what I found:
DECLARE @Rows INT,
SET @BatchSize = 2500;
SET @Rows = @BatchSize;
WHILE (@Rows = @BatchSize)
UPDATE TOP(@BatchSize) db1
SET db1.attr = db2.attr
FROM DB1 db1
LEFT JOIN DB2 db2
ON db1.attr2 = db2.attr2
SET @Rows = @@ROWCOUNT;
I simplified my statement a little bit as you can see, but it should still be clear how I approached the whole problem.
However, this thing loops forever, and when looking at the output it changed much more rows than there are in the database.
I checked the same loop with a select statement inside later on and found out that it seems to simply select the first @BatchSize rows of the table on and on, even though I thought it would progress in the index with every iteration.
How can I change this so it actually does progress by @BatchSize indices every iteration instead of simply targeting the same rows everytime?