[UPDATE: 2/20/19]
I figured out a pretty trivial solution to solve this problem.

```
CREATE TRIGGER TriggerClaims_History on Claims
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON
    INSERT INTO Claims_History
    SELECT name, status, claim_date
    FROM Claims
    EXCEPT SELECT name, status, claim_date FROM Claims_History
END
GO

```

I am standing up a SQL Server database for a project I am working on. Important info: I have 3 tables - enrollment, cancel, and claims. There are files located on a server that populate these tables every day. These files are NOT deltas (i.e. each new file placed on server every day contains data from all previous files) and because of this, I am able to simply drop all tables, create tables, and then populate tables from files each day. My question is regarding my claims table - since tables will be dropped and created each night, I need a way to keep track of all the different status changes. 

I'm struggling to figure out the best way to go about this.

I was thinking of creating a claims\_history table that is NOT dropped each night. Essentially I'd want my claims\_history table to be populated each time an initial new record is added to the claims table. Then I'd want to scan the claims table and add a row to the claims\_history table if and only if there was a change in the status column (i.e. claims.status != claims\_history.status).

Day 1: 

```
select * from claims          

id | name     | status
 1 | jane doe | received

select * from claims_history  

id | name     | status   | timestamp
 1 | jane doe | received | datetime

```

Day 2: 

```
select * from claims          

id | name     | status
 1 | jane doe | processed

select * from claims_history  

id | name     | status    | timestamp
 1 | jane doe | received  | datetime
 1 | jane doe | processed | datetime

```

Is there a SQL script that can do this? I'd also like to automatically have the timestamp field populate in claims\_history table each time a new row is added (status change). I know I could write a python script to handle something like this, but i'd like to keep it in SQL if at all possible. Thank you.