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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

You can use   trancount in MSSQL  From the documentation      Returns the number of BEGIN TRANSACTION statements that have occurred on the current connection

[sql-server] How to get number of rows inserted by a transaction

Examples related to sql-server

Examples related to sql-server-2005