send an email if an insert statement from staging table records do not match the master table

Fordraiders
Fordraiders used Ask the Experts™
on
sql server 2008 r2

I have a job that runs every night.
In the sql , I need some type of email alert that will tell me if after an "insert statement" was completed;
If the records that were in a staging table has the same count as in my master table ?

I cant find any code reference to my  question ?

Thanks
fordraiders
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Software Team Lead
Commented:
few things you can take into considerations

I have a job that runs every night.
Is that calling a stored procedure? if yes then it's good, you can write the whole logic within.

In the sql , I need some type of email alert that will tell me if after an "insert statement" was completed;
To send an email, you can refer to sp_send_dbmail:

sp_send_dbmail (Transact-SQL)
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-send-dbmail-transact-sql?view=sql-server-2017

To detect the insertion of records, you can try to check the record count in that table.

declare @recordcnt int = 0
Select @recordcnt = count(*)
from yourResult

print @recordcnt

Open in new window


If the records that were in a staging table has the same count as in my master table ?
you can use similar codes posted above to compare "record count" from 2 different tables.

hope this helps
ste5anSenior Developer

Commented:
Step 1: Enhance your job (procedures behind), so that you log this information into a separate table.
Step 2: Use sp_send_dbmail as Ryan wrote in a separate job scheduled later to collect information from your log to generate and send that e-mail.

Advantage: You can collect information about more then the ETL job to provide an summary e-mail of your daily system state as a single e-mail.

Important thing here: You must send the e-mail every day, even when there is nothing to report. Otherwise you would not notice a system failure.

Author

Commented:
thanks !!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial