Identifying Changed Data

[briangriffin]

I've started getting a flat file each day of all employees and the usual HR data - not a lot, about 700 employees and 20 fields. Every employee is in the file every day.

Right now I'm working on the ETL piece to get it all into a table, and I think I'll store all the data each day for auditing purposes. Ultimately I need to produce a file each day of any new employees (no problem there) but also any employee where any piece of data has changed from the previous day. I can pull everything out of the history table directly into the report, or I could load those changed records into another table to make the reporting piece a snap.

This is a classic type of request so before I start lumbering through it I thought I'd get some advice on the best approach from people who have done it before. Thanks in advance.

 

[philhege]

Plenty of useful approaches here:

https://www.simple-talk.com/sql/t-sql-programming/on-comparing-tables-in-sql-server/

-----------
With business clients like mine, you'd be better off herding cats.

 

[Olaf Doschke]

The article mentions storing a checksum value with the data. While the article also shows the checksum equality of differeing words, the whole record checksum will most seldom be identical on a changed employee record.

Another thing you can put into any record to detect any change is a timestamp, they automatically allter with any change. So when you join yeaterdays data with todays on ID and find different timestamp you know something has changed in that ID.

Even better, when you query any data with timestamps larger than the highest timestamp of yesterday you get all changes since yesterday including new entries.

Bye, Olaf.

 

[flapeyre]

You can compare yesterday's and today's record in the flat file (before you import it). If the employee number is the first field, you can use that as sort of a key for the two records. If it's a fixed-length file, all the better; just compare using SUBSTRING. Otherwise, you'd have to parse the record to get the employee number for matching.



-- Francis
Francisus ego, sed non sum papa.