[SQL] How to insert records where there is a change to one field SQL

I have a table of tickets created that looks basically like this:

ID Created Status Insert_date
1 10/30/2024 New 10/31/2024
2 10/31/2024 New 11/01/2024

I am currently exporting data from a Sharepoint list as a csv and importing the data to a staging table where I want to import "New" records (where the ID for the ticket is not currently in the table. I also want to import rows where there has been a change to the Status of an existing ticket ID to keep track of changes historically. So lets say the staging table after an import looks like this

ID Created Status
1 10/30/2024 In Progress
2 10/31/2024 New
3 11/02/2024 New

The final table after the insert script runs should look like

ID Created Status Insert_date
1 10/30/2024 New 10/31/2024
1 10/30/2024 In Progress 11/02/2024
2 10/31/2024 New 11/01/2024
3 11/02/2024 New 11/02/2024

So now there should be two rows for ticket ID 1 since there was a change, and a new row for ticket ID 3. No changes to ticket ID 2 so no new rows. 11/02/2024 being todays date. The primary key is a combo of ID and Status.

I know how to insert a new ID by doing a left join of the final table with the staging table

INSERT INTO dbo.Tickets (ID,Created,Status,Insert_date)
SELECT b.ID,b.Created,b.Status,CAST(GETDATE() as date)
FROM dbo.Tickets_Staging b
LEFT JOIN dbo.Tickets a on b.ID = a.ID
WHERE a.ID is null


However I am stuck as to how to also insert rows where there was a change in Status to the Ticket like there was for ticket ID 1.

Continue reading...