CODEWITHSUNDEEP
sql-servertransactionssql-server-2016
user11156893
user11156893

Reputation:

Tables: How Many Rows Inserted and Updated

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.

Upvotes: 3

Views: 4059

Answers (2)

Rima
Rima

Reputation: 1455

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

Upvotes: 5

Xabi
Xabi

Reputation: 483

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).

Upvotes: 0

Related Questions