 the source to the target table. To identify these changes, we will use the data in the "LastChange" column in the following way: first, we will find the maximum value in the "LastChange" column in the target table. After that we will choose only the rows from the source table where the value in the "LastChange" column is greater than the maximum value of the "LastChange" column in the target table and finally we will update the target table by using these rows from the source table. In this way we do not update rows on the target which have not changed in the source table after the last synchronization. We will use the MERGE command to implement this data synchronization process:
After running this command for the first time in our test environment, all of the data in the source table will be inserted into the target table, because the target table is empty:
Now let’s do some updates in the source table:
After that let’s run the merge command again:
We will see that all changes made in source table are applied to the target and now the SourceTable and TargetTable tables are fully synchronized:
To automate this data synchronization process you can create a synchronization script, stored procedure or SSIS package that runs on a predefined schedule.
All in all, the rowversion (timestamp) data type is very useful to detect data changes. They are synonymous data types, however as we can see in this tip, there are some differences between choosing one of them during implementation. Also, Microsoft recommends to use rowversion instead of timestamp, because it will be deprecated in future versions. Using the rowversion data type to track changes can be the best solution, especially when other solutions are not applicable to the given task. This method is often used in loading data warehouses.