Hi,
I want to do run a very large UPDATE Statement covering millions of
records. I saw some material in the web that shows how to acheive this
using SET ROWCOUNT, Infinite WHILE LOOP and @@ROWCOUNT.

I am not getting the approach. How can i write a update statement to
UPDATE records batch by batch to minimize transaction logging using
T-SQL?

Any pointers will be of great help.

Thanks in advance

The idea is that after each batch there are less items to update and
therefore when @@rowcount gets to 0 then you are finished

Imagine we have a table

CREATE TABLE InABatch(col1 int)
GO
INSERT InABatch VALUES(0)
INSERT InABatch VALUES(0)
INSERT InABatch VALUES(0)
INSERT InABatch VALUES(0)
INSERT InABatch VALUES(0)

We have five rows but i want to only update 2 rows at a time so i will have
to issue an UPDATE statement *4


SET ROWCOUNT 2
declare @i int
set @i = 100

WHILE @i > 0
BEGIN
UPDATE InABatch SET col1 = 1 WHERE Col1 = 0
SET @i = @@ROWCOUNT
END


(2 row(s) affected)


(2 row(s) affected)


(1 row(s) affected)


(0 row(s) affected)


set rowcount 0
select * from InABatch



--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.Konesans.com


"Karthik R" <dtsguy1 (AT) rediffmail (DOT) com> wrote

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

Thanks Allan! Thats Great!!