none
Compare and synchronize two tables/view RRS feed

  • Question

  • Hi, The task is to compare and synchronize two tables located on different servers.

    I tride to use TableDiff.exe to do it but 

    [CODE]tablediff.exe -sourceserver Serv1 -sourcedatabase DB1 -sourceschema export -sourcetable vw_TAb1 -sourceuser User_read -sourcepassword Pwd -destinationserver Serv2 -destinationdatabase DB2 -destinationschema dbo -destinationtable vw_Tab2 -f C:\Temp\Tab1.sql  [/CODE]

    gives an error message:

    Table [DB1]. [Export]. [Vw_TAb1] on Serv1 and Table [DB2]. [Dbo]. [Vvw_Tab2] on Serv2 have different schemas and can not be compared.
    And on the server sources all the tables are View and for some works correctly for some there.

    The question is how to make this utility work or there is another way to implement table synchronization (without using paid)

    Saturday, October 21, 2017 7:00 AM

All replies

  • Hi

    When two databases have so radically different schemas you should be looking at techniques for data migration or replication, not synchronization. SQL Server provides two technologies for this, SSIS and Replication, or you can write your own script to do this. 

    Replication will take new or modified data from a source database and copy it to a target database. It provides mechanisms for scheduling, packaging and distributing changes and can handle both real-time as well as batch updates. To work it needs to add enough info in both databases to track modifications and matching rows. In your case it would be hard to identify which "Products" have changed as you would have to identify all relevant modified rows in 4 or more different tables. It can be done but it will require some effort. In any case, you would have to create views that match the target schema, as replication doesn't allow any transformation of the source data.

    SSIS will pull data from one source, transform it and push it to a target. It has no built-in mechanisms for tracking changes so you will have to add fields to your tables to track changes. It is strictly a batch process that can run according to a schedule. The main benefit is that you can perform a wide variety of transformations while replication allows almost none (apart from drawing the data from a view). You could create dataflows that modify only the relevant Product field when a Product related Attribute record changes, or simply reconstitute an entire Product record and overwrite the target record.

    Finally, you can create your own triggers or stored procedures that will run when the data changes and copy it from one database to the other.

    you could start here:

    https://technet.microsoft.com/en-us/library/ms169917(v=sql.110).aspx

    Saturday, October 21, 2017 7:31 AM