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

  • 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

  • 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 ;
  • 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

  • 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
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

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

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

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

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

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