61

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 ?

HotTester
  • 5,380
  • 15
  • 59
  • 95

4 Answers4

128

@@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
KM.
  • 98,537
  • 33
  • 172
  • 205
  • 2
    Instead of using `SELECT @Rows=@@ROWCOUNT`, I tried `SET @Rows=@@ROWCOUNT` and still received the same results. Thank you, nice post! – MikeTeeVee Jun 19 '15 at 12:03
  • FYI: _Even_ wrapping my INSERT statment in Transaction block statements caused the @@RowCount to be reset, so I had to set the @Rows variable before calling Commit! – MikeTeeVee Jun 19 '15 at 12:16
  • 1
    @MikeTeeVee by using `SELECT` you can make multiple assignments in a single statement, so you can easily add `SELECT @Rows=@@ROWCOUNT, @ID=SCOPE_IDENTITY(), @Error=@@ERROR`. With `SET` you can only set one value. `TRY CATCH` eliminate the need for capturing `@@ERROR`, but I still use `SELECT`. – KM. Jun 19 '15 at 13:27
  • 2
    @MikeTeeVee, You must capture `@@ROWCOUNT` immediately after the command you are interested in because it is set following every command, even `COMMIT`, even your `SET @Rows=@@ROWCOUNT`. For example, if you update 4 rows and then commit, your `@@ROWCOUNT` will be 4 after the `UPDATE` but will be zero after the `COMMIT`. That is the reason for capturing `@@ROWCOUNT` in a local variable. – KM. Jun 19 '15 at 13:28
  • I tried this method. It works great on Management studio but when i try to execute the same query in my code it always returns `0` for `rows` and `1` for `rowcount`. I am not sure if there's anywhere I am going wrong – Wairimu Murigi Jul 15 '15 at 17:07
  • `@@ROWCOUNT` is bad practice. For ad-hoc scripts it is fine, but for *production* scripts it is just a BUG waiting to happen. It requires only one programmer that missed the `@@ROWCOUNT` and inserted some code between the insert and the `@@ROWCOUNT`. – andowero Jan 11 '22 at 22:34
11

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

Community
  • 1
  • 1
ips1 38
  • 179
  • 1
  • 5
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
Fernando Torres
  • 900
  • 7
  • 18
-1

You can use @@trancount in MSSQL

From the documentation:

Returns the number of BEGIN TRANSACTION statements that have occurred on the current connection.

Stephan Bauer
  • 8,611
  • 5
  • 37
  • 57
NightFury
  • 9
  • 1
  • 1