At work, I have a table that has a gazillion rows. Last night, I had to update a column with a new value and sometime in the night it failed! FAILED. because the log file got fulled. Fine fine, I added another log file, set it to unlimited growth, what do I care, I just want the thing to finish! 5 hours later, it failed. ARRRG!
I started thinking, how can I update this gazillion row table in batches of 500k rows using the UPDATE statement. The UPDATE statement itself doesn’t allow any sort of batch processing, they should of included it. Stupid developers. Anyways, another way of doing it by batch would be to capture the row number, have three variables @maxRowCount, @minRow and @maxRow, and have a while loop loop thru it or something.
The simplest way would be to use ‘set rowcount’. The code I used to updated in batches of 500k look as follow:
