Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2003

    Unanswered: Sync-ing data between databases


    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).

    My questions:
    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
    FROM source.Employees
    WHERE EmployeeID NOT IN
    (SELECT EmployeeID FROM destination.EmployeeID)

    Does anyone have any suggestions? Thanks very much for the help!

  2. #2
    Join Date
    Aug 2002
    You can use DTS to specify the tailor-made queries to affect the changes.
    --Satya SKJ
    Microsoft SQL Server MVP

  3. #3
    Join Date
    Jun 2003

    Sync-ing data between databases

    Thanks....but is it possible to query the DESTINATION database in the transformation query? All the BOL examples seem to only show how to query the SOURCE database.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts