Replicating Data Between a Flat-File System and SQL Server

I have a flat-file system and a SQL Server database that need to reflect changes to each other's systems. Completing the initial data upload is easy, but replicating the changes in each data store is difficult, especially changes in the flat files. How can I replicate the data between a flat-file system and SQL Server?

One way to replicate the data between the flat-file system and the database is to log changes to both systems. For example, you could log all changes as INSERT, UPDATE, or DELETE, then write programs to apply these files to the other system. However, if you choose this approach, you'll need to cope with three collision types: updates to records that aren't there, deletes of records that aren't there, and inserts of records that are already there.

If the default action doesn't make sense or is impossible, you need to decide what action to take. For updates to nonexistent records, you can usually confirm that the record hasn't been deleted, then perform an insert; if the record has been deleted, you can ignore this operation. You can usually ignore deletion of records that have already been deleted. For inserts of records that are already there, you can convert the new record to an update or ignore the operation.

SQL Server also lets you put triggers on tables in which you want to capture changes. To extract the changes from a flat-file system, you'll probably have to recode the system a bit or build a system that checks the end-of-day files against a previous day's version. Note that letting both systems update the same entities isn't a good idea. We suggest that you move to a master system that performs all updates and applies changes to both systems.