In an accident logging database we have a table of accidents and some of those relate to members of staff. When a new accident is recorded I have scheduled job to copy relevant data (12 columns) into the HR system. This is fine and dandy.
I now need to set up a step in the job to look for records in the accident database that have been updated since the last transfer of data and update those records in the HR system.
A) add a column to the two tables in the two databases that can be set to the most recent update time and use that to select the rows for update?
B) add a bit column to the Accident table and set that to 1 when I want that row transferrred and set it back to 0 when ever the transfer has completed?
C) do some sort of check on each of the column values and update the data whereever I find its different?
D) something else ... ?
The HR record is not updated so this is one-way data flow from the Accident database table.
I think I know which answer I would prefer but getting your ideas may convince me otherwise.
Oh, no SSIS available and its not heavy duty enough to warrant replication - its maybe 20-30 accidents per month.