How the tool synchronize two database tables?

During synchronization, the data comparer makes the contents of the secondary table equal to the contents of the primary one. There are three steps:

  1. Data comparison.
  2. Synchronization script generation.
  3. Synchronization script execution.

It is important to understand that data synchronization based on primary/unique key values. Please be careful when selecting a custom key. The incorrect custom key definition is the most possible reason of unexpected synchronization issues.

In this process records absent in the primary table will be deleted from the secondary one, records that differ will be updated, while records absent in the secondary table will be inserted from the primary one.
The user can change this behavior using the following options:

  1. Delete from secondary - disabling this option will block deleting a record from the secondary table even if there is no such record in the primary table.
  2. Insert by primary - disabling this option will block adding records from the primary table even if there are no such records in the secondary table.
  3. Update secondary - disabling this option will block secondary rows modifications (updates).

The user can prevent script execution using related option. In this case, the script will be produced without actual database modification.

There are two places for output SQL script location definition: project level and product level. In the first case, different projects will use different output files. In the second common file will be used. If necessary we recommend using $DATE$ and $TIME$ macros in the script file name to generate unique output files.

The program stops at the first error happen during synchronization script execution. This option can be switched off. Moreover, the program can rollback changes already made if the error happen. Please be sure that specified transaction size is enough to cover all changes.

DTM Data Comparer can add optional COMMIT statements to the synchronization script. Also, the user can specify custom SQL statement delimiter like 'go' or ';'.

The "Date Conversion Function" feature is useful when you synchronize date and time values with the different format: different databases, locations, culture, etc.

The program shows following message for large synchronization scripts. It is recommended to run the whole script in this case.
DTM Data Comparer: large script warning

Note for Microsoft SQL Server users: there two modes for IDENTITY columns. By default, the database comparison tool ignores identity column i.e. generates no code to synchronize. In the second case, the program will synchronize these values as well.

See also: data synchronization settings.