print number of update records through sql job

[sqlnovice]

I have update statement which updates column name (auditor_details) in a
churn of 5000 records at a time of auditor table which contain 3.2 millions
records. I have schedule this through sqljob. I'm looking over sql example to
check for update records and gives a output and quit the jobs if no update
records.

Thanks in advance

[Erland Sommarskog]

There are two ways to go, either a loop over the clustered index:

SELECT @id = min(id) FROM tbl
SELECT @incr = 50000
WHILE EXISTS (SELECT * FROM tbl WHERE id >= @id
BEGIN
UPDATE tbl
SET col = ...
WHERE id BETWEEN @id AND @id + incr - 1

SELECT @id = @id + @incr
END

Here I've assumed that id is an integer, but it can also work with dates,
in which case you need to find a good interval. Maybe 30 days.

The other where to go is

WHILE 1 = 1
BEGIN
UPDATE TOP(@batchsize)
SET col = ...
WHERE ...

SELECT @@rowcount= @rowc

IF @rowc <> @batchsize
BREAK
END

This presumes that can identify which rows you have processed.

The first method is likely to be faster, since SQL Server does not
have to scan the table each iteration.

Then again, for only 3.2 million rows I might consider doing it all
at once, unless it's a LOB column you are updating.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx