asked on

SQL and duplicate records

I've recently set up a ODBC connection to our webhost's SQL server. For a web application that they've built for us, they're writing data to it that get's over-written by users as they come in and update their data. What we'd like to do is query the server every 5 minutes (we have a script and the eSchedule database doing that automatically) and get the latest data. However, we'd like to preserve a history of all records. Ideally, it would be best I could create a script that went and got the recrods that have changed (and new ones) and download those. We would base all of our reporting off of that customer's latest record, and we'd just want the history as a way to refer to what they've done. I know how to do matching, updating, etc. during import-- but I'm not sure if there's a way to utilize those functions during the import process. I'm trying to avoid downloading all the records every 5 minutes (as that would be one possible solution). Any thoughts?

ASKER CERTIFIED SOLUTION

Member_2_908359

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

wesbrowning

ASKER

Yes- There is a timestamp field (ie. 01-01-2006 14:03:54) in the SQL database and there are customer ID and order ID fields to help identify the uniqueness of that record. This unique identifeier is only in one of the main tables though. However, the data may change to that customer's related records in other tables. How would I go about getting (through the query and scripting) the new and updated records, as well as the related records in other tables?

Member_2_908359

you need a time stamp in every related table too, or something which updates the customer record in the main table every time a related table is changed. If you don't have this implemented already and can't alter anything on SQL side, I am afraid there is no smart solution, at least I can think of now. I'll be back if the genius hits me!

wesbrowning

ASKER

Ok- I think we can do that. I'll talk to our web developer and get them to add it into each table. What would I do next?

Member_2_908359

you query every table for records of which the timestamp is > than the more recent you have on filemaker side for the same table

billmercer

I'd suggest using two timestamps, one for when the record was modified, and one for when it was first created.

Member_2_908359

I agree on using creation and modif. timestamps. Also make sure both clocks are sync'ed, FM uses the OS clock, and this may be different from the SQL server's.
So, I'd recommand a tool to make sure both machine are on the same time.