Home How do I update a SQL table in batches?

# How do I update a SQL table in batches?

user1349
1#
user1349 Published in March 18, 2018, 5:11 pm
 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, @BatchSize INT; SET @BatchSize = 2500; SET @Rows = @BatchSize; WHILE (@Rows = @BatchSize) BEGIN UPDATE TOP(@BatchSize) db1 SET db1.attr = db2.attr FROM DB1 db1 LEFT JOIN DB2 db2 ON db1.attr2 = db2.attr2 SET @Rows = @@ROWCOUNT; END;  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?
 You need to login account before you can post.
Processed in 0.313852 second(s) , Gzip On .