Outputting number of Rows Updated

[johnpienaar]

Hi
I was wondering if anyone knew a way of updating a field with the number of rows that were updated by a specified query.
For example if I run an update query in query analyzer and it returns "2000 rows affected by query"- is there anyway for me to update another table with that value (2000)?
Help would be much appreciated.
John

 

[JamesLean]

Something like this:

DECLARE @row_count int

--do your first update here
UPDATE table1...

--get the number of rows affected
SET @row_count = @@ROWCOUNT

--insert rowcount into your table
INSERT table2 VALUES (@row_count)

--James

 

[rac2]

Within a stored procedure you may obtain the number of rows affected by the last statement in a variable named @@ROWCOUNT.

DECLARE @nRowsAffected INT
. . .
UPDATE MyTable SET work_completed = 1 WHERE answer IS NOT NULL
SET @nRowsAffected = @@ROWCOUNT

UPDATE MyTrackingTable SET last_update_count = @nRowsAffected

 

[johnpienaar]

Thanks for the quick replies- I'll test these out now.