If you ever try to update several millions of record in single Update statement then think several times because it can cause real pain.<\/p>\n
Disadvantage of single Update statement is … Its considered as single transaction and it doesn’t commit all changes until its done so if you have large number of records to update then it may fill up your transaction log causing disk full error<\/p>\n
Solution to this problem is run it in batches … using technique shown below. Key thing to notice is SET ROWCOUNT XXXX<\/strong><\/p>\n <\/p>\n If you ever try to update several millions of record in single Update statement then think several times because it can cause real pain. Disadvantage of single Update statement is … Its considered as single transaction and it doesn’t commit …<\/p>\nUse Northwind\r\n\r\nselect * into #tmpCsutomer from dbo.Customers\r\n\r\nSET NOCOUNT ON\r\n\r\ndeclare @totcounter integer\r\ndeclare @msg varchar(max)\r\ndeclare @batchsize int\r\n\r\nset @batchsize=10 --\/\/ <<<<<< batch size\r\nset @totcounter = 0\r\n\r\nSET ROWCOUNT @batchsize\r\n\r\nDECLARE @rc int\r\n\r\nWHILE (1=1)\r\nBEGIN \r\n--LOOP START\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\r\n\r\n\tupdate #tmpCsutomer\r\n\tset region = null\r\n\twhere customerid in (select customerid from Customers)\r\n\tand region is not null\r\n\r\n\tset @rc=@@ROWCOUNT\r\n\r\n\tIF @rc != @batchsize\r\n BREAK\r\n\r\n\tSET @totcounter = @totcounter + @batchsize\r\n\r\n\tset @msg = 'Rows Processed...' + cast(@totcounter as varchar(20))\r\n\tprint @msg\r\n--LOOP END\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\r\nEND\r\n\r\n--PRINT LAST MSG \r\nSET @totcounter = @totcounter + @rc\r\nset @msg = 'Rows Processed...' + cast(@totcounter as varchar(20))\r\nprint @msg\r\n\r\nSET ROWCOUNT 0<\/pre>\n","protected":false},"excerpt":{"rendered":"