I have a web application that runs on SQL 2000. I would like to develop a way to synchronize the data on the "Employees" table of another database (MS Foxpro) with the "Employees" table on my SQL2K database. Basically every change that is made on the Foxpro database should be updated on the SQL2K database (but not necessarily vice-versa).
1. Is DTS Import/Export Wizard the best option to schedule this batch run?
2. DTS is good for importing all the data, but I only want to import data that (a) doesn't exist on the destination database and (b) is different from the destination database.
In the case of (a), an INSERT should be done.
In the case of (b), an UPDATE should be done.
But the problem is I can't QUERY the destination database before inserting, and I don't know if there's a way to do an update using DTS.
So how would I be able to query the destination using something like:
INSERT INTO destination.Employees (Name, Email)
SELECT Name, Email
WHERE EmployeeID NOT IN
(SELECT EmployeeID FROM destination.EmployeeID)
Does anyone have any suggestions? Thanks very much for the help!