SSMS Help - Capturing Changes in the data and recording

Copper Contributor

Hello

 

Sorry in advance - I am new to SQL and SSMS. Please can I have some help with learning if the below is possible?

 

I will have an SSIS package feeding data to SQL with the below table. The 'Report_Completion_Date' field is a write over field so if 'Client_ID' 1 completes a new report on 02/06/2023, the field will change from 01/06/2023 to 02/06/2023 when I next feed the data in on 02/06/2023. I can set up the SSIS package to inject data daily to capture any changes

 

Client_IDReport_Completion_Date
101/06/2023
215/06/2023

 

I would like to capture any changes in the Report_Completion_Date field for any Client_ID's.

 

My ideal output table will show the Client_ID and any or all Report_Completion_Date, unsure if it is best/possible to show the different Report_Completion_Date as additional columns if there is new changed data for Client_ID, for example:

Example for new row everytime there is a new 'Report_Completion_Date'

Client_IDReport_Completion_Date
101/06/2023
102/06/2023
215/06/2023

Example for new column everytime there is a new 'Report_Completion_Date'

Client_IDReport_Completion_DateReport_Completion_Date_2
101/06/202302/06/2023
215/06/2023Blank/Null

 

Thanks so much for your help

2 Replies

@threw000 , SSMS is just a tool, nothing else, the job is always done be the SQL Server database engine.

You can use a DML trigger to log changes, see

CREATE TRIGGER (Transact-SQL) - SQL Server | Microsoft Learn

@threw000 Hi if I understand

 

1) I would recommand to use change data capture feature on base table with data in case you work with sql server standard/enterprise/developer edition as described here - https://janzednicek.cz/en/sql-server-change-data-capture-cdc-tracking-changes-in-a-table/

 

2) in case you use express edition, then recommandation nr 2 is a trigger as someone here stated also on base table - https://janzednicek.cz/en/sql-triggers-in-sql-server-definition-types-syntax-and-examples/

 

3) in case you cant do any of above just craft ssis package doing snapshot of base table (full load) and save this snapshot into temporary table. After that identify increment by usint except command and insert it into destination table. hope it helps