Update with rowcount

Question

Update with rowcount

Tara-1044200

SSCoach

Points: 15951
More actions
January 21, 2010 at 10:18 am

#137510

I have table Employees (Id,empid,empno,state)
Updating the table as
update Employees
SET empno=EmployeeNo
From Employees inner join OldEmp on empid=empid
where empno like 'CDW%'
From the above update I want to capture the following
1. no of recrods updated
2. state of the record which updated
3. Table Name where update ran

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

You can check the value of @@rowcount in order to know how many records got updated. The name of the table is just like the name of the table in your update statement (employee). As for the second question, I didn’t understand it.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Tara-1044200 SSCoach Points: 15951 More actions · Answer 2

I just need to know how many recrods updted on which table and value from the coumn "state"

I want to insert them into a audit table (Tablename,RowsUpdated,State)

OTF SSCertifiable Points: 6104 More actions · Answer 3

You may find TSQL's Output Clause helpful. You can check BOL for details.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

This is one of the very few places where I'd condone the use of a Trigger. Read about triggers in Books Online.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)