Reseed applying ‘incorrect’ identity values

After synchronizing data with the help of Data Compare for SQL Server, the next time you insert data in the synchronized table, you may get an error saying that the automatically-generated value violates a primary key or unique constraint.

To get the Data Compare tool automatically reseed identity values after synchronization, you need to select the Reseed identity columns option when configuring Data Synchronization Wizard. Data Compare will gather the current identity value from the source table and apply it to the target table. Thus, identity columns with a primary key constraint will continue generating valid identity values.

However, there are some situations, where the current identity value in the target database may become or remain invalid:

• Data was updated either before the synchronization or at the same time as the data comparison.
• The direction of synchronization was changed. In this situation, the data sources need to be compared again because the identity value is gathered at registration-time.
• In case, there are no differences in the table, Data Compare for SQL Server does not reseed and the identity values remain different.
• The records were deleted from the source table via a DELETE query. In this situation, the identity is not rolled back and Data Compare will synchronize the current identity, which can appear higher than expected. You can roll the identity back to the base seed value using the TRUNCATE TABLE operation.