The ways of Alerting when comparing two tables columns and find differences..

Question

The ways of Alerting when comparing two tables columns and find differences..

asita

SSCarpal Tunnel

Points: 4144
More actions
July 28, 2021 at 4:24 am

#3914530

Hello Good Morning Guru's
Can you please help me to give some ideas or links that I can use it in my situation.
I have two tables, both tables have EMPID as primarykey.
Here DailyStageTable is truncate and get loads daily.where as FinalTable is the main table it has all data only appends no deletion happens in this FinalTable
1) FinalTable (EMPID, Col1,Col2, Col3, Col4, Col5, Col6, Col7, InsertDate, UpdateDate)
2) DailyStageTable (EMPID, Col1,Col2, Col3, Col4, Col5, Col6, Col7)
so here, I have a daily job that executes the procedure, the procedure logic does 2 things
1) insertion of new records from DailyStageTable to FinalTable (based on EMPID) also populates Insertdate (getdate() of procedure execution)
2) updates the remain values Col1,Col2,Col3,Col4,Col5,Col6, Col7 if any of these values were differet between both tables for an EMPID.
so far it looks good, but now I should not allow utomatic updates for Col3, Col6 (using the update statement in procedure) instead I need to alert with the data in it so the update will handle manually by some person..
so here my question is any idea on "ALERT" thsee two columnc changes if any?
what could be best possible ways to do it, this can be an email or stage them in some table or in same table...
please Advise..
Asita

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ant-Green SSC Guru Points: 113507 More actions · Answer 1

I would build in a sp_send_dbmail part to the procedure which does the updates and emails out a result set based on a query.

EXEC msdb.dbo.sp_send_dbmail  
    @profile_name = '<YourDBProfile>',  
    @recipients = '<YourEmailAddress>',  
    @query = 'SELECT FT.EMPID, FT.Col3 AS OriginalCol3, DST.Col3 AS NewCol3, FT.Col6 AS OriginalCol6, DST.Col6 AS NewCol6
FROM FinalTable FT
INNER JOIN
DailyStageTable DST
ON FT.EMPID = DST.EMPID
WHERE FT.Col3 <> DST.Col3
OR FT.Col6 <> DST.Col6' ,  
    @subject = 'EMP COL3 / COL6 Differences',  
    @attach_query_result_as_file = 1 ;

asita SSCarpal Tunnel Points: 4144 More actions · Answer 2

Thank you very much, this is good.

but we are looking to set up something such as writing to a table so the user can query it and change accordingly.

the reason for this manual process is there is some quite more columns for each table so just checking to see if there is any better way rather than sending multiple emails for each set of table for each column?

any suggestions please.

Thank you a ton in advance

Sincerely

asita

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 3

In your procedure you would have 3 statements. The INSERT to add new rows to the final table, an UPDATE to update those rows in the final table where the columns you are checking have changed - and a final INSERT to your 'other' table where the columns you want manually updated have changed.

You can identify the rows using EXCEPT:

SELECT EmpID, col3, col6 FROM sourceTable
EXCEPT
SELECT EmpID, col3, col6 FROM finalTable

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

asita SSCarpal Tunnel Points: 4144 More actions · Answer 4

Got it. Thank you for the update, I will try this and update you soon.

asita SSCarpal Tunnel Points: 4144 More actions · Answer 5

Any other ways or samples please that you aware of this kind of examples? please