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?
wesbrowningAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lesouefCommented:
I think you should have a field which allow you to recognize new and modified fields. then you'll be able to select the right records.
You should keep the data/time of the last update (lets call it ref) or dtermine it by checking the most recent record in yr database, and query records of which modification date/time is > ref  or empty (provided the initial state for that field is empty)
Does the SQL database has an automatic field keeping the modification date/time stamp? Or is the web app filling such data somewhere?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
wesbrowningAuthor Commented:
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?
lesouefCommented:
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!
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

wesbrowningAuthor Commented:
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?
lesouefCommented:
you query every table for records of which the timestamp is > than the more recent you have on filemaker side for the same table
billmercerCommented:
I'd suggest using two timestamps, one for when the record was modified, and one for when it was first created.
lesouefCommented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
FileMaker Pro

From novice to tech pro — start learning today.