Tables: How Many Rows Inserted and Updated

Question

How do I find number of rows inserted in a table, and number of rows recently updated? We had a data warehouse stored procedure today, which updated a table. I don't care which rows, just the counts (Inserted/and updated) from each transaction. Our database does not have CDC/or CT. Is there a way to query the Sys DMVs or logs? I am inheriting legacy code, so will add manual logging in future, just curious if SQL Server has this auto logged anywhere.

Answer 1

This might help you, We can achieve this in two ways.

1. OUTPUT Clause : Returns information from, or expressions based on, each row affected by an INSERT, UPDATE, DELETE, or MERGE statement. These results can be returned to the processing application for use in such things as confirmation messages, archiving, and other such application requirements. The results can also be inserted into a table or table variable. Additionally, you can capture the results of an OUTPUT clause in a nested INSERT, UPDATE, DELETE, or MERGE statement, and insert those results into a target table or view.

2. @@rowcount : Returns the number of rows affected by the last statement. If the number of rows is more than 2 billion, use ROWCOUNT_BIG.

CREATE TABLE TEMPS
(
    ID INT,
    ENTRY_DT DATETIME,
    BIRTH_DT DATETIME,
    NAMES VARCHAR (25)
)
/*----------------------1. Get Inserted & Updated record using @@ROWCOUNT-------------------------------- */
declare @rowcnt int

INSERT INTO TEMPS 
VALUES ('123', '6/10/2015', '2/6/2018', 'JOHN'),
       ('123', '2/4/2018', '2/6/2018', 'SMITH'),
       ('123', '2/4/2018', '2/6/2018', 'DOE')
set @rowcnt = @@rowcount
update temps
set Names ='rima'
where temps.Names = 'SMITH'

set @rowcnt = @rowcnt+@@rowcount

select @rowcnt "total_rows_affected"



/* ----------------------2. Get Inserted record count using Output Clause-------------------------------- */
DECLARE @MyTableVar_Inserted table( Inserted_Cnt int );  

INSERT INTO TEMPS 
output inserted.ID
Into   @MyTableVar_Inserted
VALUES ('123', '6/10/2015', '2/6/2018', 'JOHN'),
       ('123', '2/4/2018', '2/6/2018', 'SMITH'),
       ('123', '2/4/2018', '2/6/2018', 'DOE')
             
select * from temps
select count(*) from  @MyTableVar_Inserted



/* ----------------------Get Updated record count using Output Clause-------------------------------- */
DECLARE @MyTableVar_Updated table( Updated_Cnt int );  

update temps
set Names ='rima'
OUTPUT  INSERTED.ID       cnt 
INTO @MyTableVar_Updated 
where temps.Names = 'SMITH'

select count(Updated_Cnt) from @MyTableVar_Updated