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.

Unfortunately, information about table changes will not be stored unless you enabled 'Change Tracking' or 'Track Columns Updated' on the table before you make any changes on it. — Gen Wan, Apr 12 '19 at 22:10
By any luck, do you have update_on or created_on timestamps in your table? — Pratik Bhavsar, Apr 13 '19 at 04:49
You do have things like `@@ROWCOUNT`. which *"Returns the number of rows affected by the last statement."*. Of course, you can't do that retrospectively, so if you're done the update and now want to know how many rows you updated, the answer is "you don't". If you don't capture that information at the time then that information is lost. If someone asked you "how many people that entered the building yesterday were on their phones?" you wouldn't be able to answer if you didn't record that information. If they asked you how many tomorrow, however, then you could make arrangements to find out. — Thom A, Apr 13 '19 at 08:41
Using a non-production instance restore the database backup to the point of time before the stored procedure in question ran and restore it again to the point of time after the stored procedure ran and compare the data between these two restored databases. — Vladimir Baranov, Apr 14 '19 at 00:17
All the entries I see are "how you should have done it"--if you have something like that in place, you're good, but we can't tell without seeing the code. @Vladimir's comment above offers a practical solution (I'd upvote it if it were an answer). A comment in a deleted answer suggests reviewing the contents of the log file--this is remotely possible, but in practice, not so much. Unless the original design includes some means of logging information, you are sadly out of luck. — Philip Kelley, Apr 16 '19 at 21:45

score 5 · Answer 1 · edited Dec 06 '22 at 13:40

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 Caluse-------------------------------- */
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 Caluse-------------------------------- */
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

score 0 · Answer 2 · answered Apr 16 '19 at 21:35

If you can add a column of type rowversion (aka timestamp) to the tables you want to monitor, and keep a bigint data with the last change (insert / update) that you checked, you can use a stored procedure like:

CREATE Proc CountChanges(@PrevTs bigint) as
declare @sql nvarchar(MAX)=''
set nocount on
select @sql += case when @sql='' then '' else ' union all ' end
    +'select COUNT(*) Qt,MAX(convert(bigint,' + COLUMN_NAME + ')) LC from ' + TABLE_NAME
    +' where convert(bigint,'+COLUMN_NAME+')>' + cast(@PrevTs as nvarchar(20))
from INFORMATION_SCHEMA.COLUMNS
where DATA_TYPE = 'timestamp'
set @sql = 'select ' + cast(@PrevTs as nvarchar(20))
         + ' FromTs,SUM(Qt) Qty,MAX(LC) LastTs,convert(bigint,@@DBTS) DBTs from ('
         + @sql + ') q'
exec (@sql)

It returns something like:

| FromTs |   Qty |  LastTs |    DBTs |
+--------+-------+---------+---------+
| 369912 | 23302 | 3813009 | 3817000 |

with your previous starting point (FromTs), quantity of insert/updates (Qty), last rowversion (LastTs) within tables and the highest rowversion currently used (DBTs) by your DB (your next starting point).

Tables: How Many Rows Inserted and Updated

2 Answers2