How to get number of rows inserted by a transaction

Question

I have to manage a log where i have to see the number of rows that are inserted by a transaction. Is there any way of doing it dynamically ?

Answer 1

@@ROWCOUNT will give the number of rows affected by the last SQL statement, it is best to capture it into a local variable following the command in question, as its value will change the next time you look at it:

DECLARE @Rows int DECLARE @TestTable table (col1 int, col2 int) INSERT INTO @TestTable (col1, col2) select 1,2 union select 3,4 SELECT @Rows=@@ROWCOUNT SELECT @Rows AS Rows,@@ROWCOUNT AS [ROWCOUNT] 

OUTPUT:

(2 row(s) affected) Rows        ROWCOUNT ----------- ----------- 2           1  (1 row(s) affected) 

you get Rows value of 2, the number of inserted rows, but ROWCOUNT is 1 because the SELECT @Rows=@@ROWCOUNT command affected 1 row

if you have multiple INSERTs or UPDATEs, etc. in your transaction, you need to determine how you would like to "count" what is going on. You could have a separate total for each table, a single grand total value, or something completely different. You'll need to DECLARE a variable for each total you want to track and add to it following each operation that applies to it:

--note there is no error handling here, as this is a simple example DECLARE @AppleTotal  int DECLARE @PeachTotal  int  SELECT @AppleTotal=0,@PeachTotal=0  BEGIN TRANSACTION  INSERT INTO Apple (col1, col2) Select col1,col2 from xyz where ... SET @AppleTotal=@AppleTotal+@@ROWCOUNT  INSERT INTO Apple (col1, col2) Select col1,col2 from abc where ... SET @AppleTotal=@AppleTotal+@@ROWCOUNT  INSERT INTO Peach (col1, col2) Select col1,col2 from xyz where ... SET @PeachTotal=@PeachTotal+@@ROWCOUNT  INSERT INTO Peach (col1, col2) Select col1,col2 from abc where ... SET @PeachTotal=@PeachTotal+@@ROWCOUNT  COMMIT  SELECT @AppleTotal AS AppleTotal, @PeachTotal AS PeachTotal 

Answer 2

In case you need further info for your log/audit you can OUTPUT clause: This way, not only you keep the number of rows affected, but also what records.

As an example of the Output Clause during inserts: SQL Server list of insert identities

DECLARE @InsertedIDs table(ID int);  INSERT INTO YourTable     OUTPUT INSERTED.ID         INTO @InsertedIDs      SELECT ... 

HTH