How to get number of rows inserted by a transaction

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 ?

Solution 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

Solution 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

Solution 3:

I found the answer to may previous post. Here it is.

CREATE TABLE #TempTable (id int) 

INSERT INTO @TestTable (col1, col2) OUTPUT INSERTED.id INTO #TempTable select 1,2 

INSERT INTO @TestTable (col1, col2) OUTPUT INSERTED.id INTO #TempTable select 3,4 

SELECT * FROM #TempTable --this select will chage @@ROWCOUNT value