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 ?
This question is related to
sql-server
sql-server-2005
@@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
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
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
You can use @@trancount in MSSQL
From the documentation:
Returns the number of BEGIN TRANSACTION statements that have occurred on the current connection.
Source: Stackoverflow.com