Forum Discussion
SSMS Help - Capturing Changes in the data and recording
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_ID | Report_Completion_Date |
| 1 | 01/06/2023 |
| 2 | 15/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_ID | Report_Completion_Date |
| 1 | 01/06/2023 |
| 1 | 02/06/2023 |
| 2 | 15/06/2023 |
Example for new column everytime there is a new 'Report_Completion_Date'
| Client_ID | Report_Completion_Date | Report_Completion_Date_2 |
| 1 | 01/06/2023 | 02/06/2023 |
| 2 | 15/06/2023 | Blank/Null |
Thanks so much for your help
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
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
