Post reply

Dynamic column selection for alerting rather updating

  • August 16, 2021 at 1:41 pm

    #3920372

    I have two Tables SourceEmployee and TargetEmployee both same column names except the targetemployee has additional column which is recordInsertdate, Recordlatestupdatedate

    Src_Employee

    Tgt_Employee

    both tables has same PK which is EMP_ID.

    so here i am trying to develop a procedure that runs everyday to check any new records / updates records exist in SourceEmployee if so insert/update to target table based on PK.

    step1

    Insert if EMP_ID is not exist in Target but exist in SourceEmployee

    Step2

    Update some columns if any changes from source based on ID

    Step3

    Notify (may be in email) to user if some column has changes in it so we dont want automatic updates to be happen here, we just want to notify the column name and Employee ID column so the user can manually update these

    something (helped from post) as below. so here my question is there anyway we can automate this? it means the column names hardcoding in the join condition it takes from a table which column names it need to consider etc that table will have tables name and column names for notification

    any advise please?

    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 TGTEMPLOYEE FT

    INNER JOIN

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

     

    Thanks

    ASita

  • August 17, 2021 at 2:10 pm

    #3920793

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

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

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