Compare COUNT results before and after operation/load in SQL

I need help for one of my cases.

1. Lets say that I have one table with only one column named CustomerID with 1500 records.
2. The CustomerID table is loaded in DB 2 times per day – 10am and 10pm
3. I want to compare the CustomerID table in the morning (10am) with the one in (10pm)

SELECT COUNT(*) from CustomerID -- 10 AM / 1500 records.

SELECT COUNT(*) from CustomerID -- 10 PM / 1510 records.

I want to check for these 10 extra records – only the count, nothing more.

The main idea is to keep track on the table and if there are no new records in 10 PM – to tell the responsible person that the table is “broken”, because the table should be a growing count number with every load.

Thanks!

Answer

I did this recently for multiple DBs and table, but can show you how to do it for just one table.

Instructions:

1. Create a stored procedure using the query below (update w/ your db and table name)

   *You will need to create the table before being able to run this

2. Put this on a job schedule for 10a and 10p

3. Check daily or create a visualization/dashboard using this new table as a data source to display whether everything was loaded as it should have been

Query:

use [YOUR DB NAME]
go

create procedure [YOURSCHEMA.YOUR_NEW_AUDIT_TABLE_NAME] as

insert into [TABLE_NAME_YOU_WANT_TO_CREATE_FOR_TRACKING]
select schema_name(schema_id) as [schemaname],
       [tables].name as [tablename],
       sum([partitions].[rows]) as [totalrowcount],
       getdate() as date_checked
from sys.tables as [tables]
    join sys.partitions as [partitions] on [tables].[object_id] = [partitions].[object_id] and [partitions].index_id in ( 0, 1 )
where [tables].name = '[TABLE_NAME_YOU_WANT_TRACKED]'
group by schema_name(schema_id), [tables].name;


go;