Just to elaborate on the architecture that we're thinking of and the order of events:
Clipper dbfs -> Clipper Staging dbfs -> SQL Staging -> SQL Replica -> SQL New table structure
1. As data gets written/updated/deleted to the live clipper dbfs a process (managed inside the clipper code) will write the record to an exact replica dbf.
2. A VB program will (via a scheduled job) DTS the clipper staging dbfs into the SQL Staging area.
3. The VB app will then transfer the data from the SQL Staging Area to the SQL Replica area. These two databases will be exactly the same structure. I plan to do this via INSERT and DELETE statements so that as new data comes into SQL Replica a trigger on these tables can add the new data to the SQL New table structure.
So what we're really trying to achieve is a clipper to SQL replication (in a new table structure). Also given the table sizes (some dbfs are 600mb large with 4-5mill records) we need to be doing incremental updates and cant simply take the entire dbf. Thats why we're using the clipper staging area which only contains the updates since the last replication.
Its a bit of a laborious process but we havent been able to come up with a better alternative.
The reason we've thought of using triggers is that we can be sure if a record is deleted/inserted in the SQL replica database we can be assured that it will get itself into the SQL New table structure.
Any comments or suggestions will be appreciated.