Progress and SQL Data 'Sync'

M

mostricki

New Member
What i am looking to do is compare data between 1 table in a progress database and 1 table in a sql database and have the data 'synced' during non business hours.

How difficult would it be to compare the tables, write the changes to a temp table (flat file?) and write the changes to the respective DB? I assume there would be two proc's one for comparing, noting the changes and writing, each way.

Can this be done thru a DTS? or how would these procedures be automated?

I am more concerned with the changes to the progress database, as i have only basic knowledge of the commands.

Thanks for your replies!
 
Here is one way:

Add triggers to your source table(s) which modify a set of fields on update or insert of a row.
That way you can track changes to individual rows and only update what has changed.
e.g.
add fields called 'modified', 'modification_datetime' etc to source table.
Every time record is updated or inserted, your trigger sets the modified and modification_datetime fields to 'Yes' and the current timestamp respectively.

Then, to synchronize your tables, you just retrieve all the records from your source table that have modified set to 'Yes' and/or have been modified in the last 24 hours and overwrite/add/delete
those entries in your target table. After/during synchronization you reset all the modified fields back to 'No'.
That won't track deletes, but you could use and additional field to mark a record as 'Deleted', 'Updated', or 'Inserted', and have your synchronization code act accordingly.
There's a bit more involved in tracking deletes, but you get the idea.

Too complicated?

Plan B:
Delete everything from target table. Import all records from source table and write them into target table. Perfect synchronization.
 
Joe,

Thanks for the Reply!

Once i extract the modified records from the SQL database how would i go about the SQL->Progress sync. is that done right in DTS? Once the SQL->Progress 'sync' is done then i would just dump the SQL table and import it from the Progress table like you suggest in Plan 'B'.

That would allow the records to be edited in both our ERP and web app.
 
You must log in or register to reply here.
Back
Top