Home How do I update a SQL table in batches?
Reply: 0

How do I update a SQL table in batches?

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:

        @BatchSize 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?

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.313852 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO