Skip to main content

Adding Change Tracking to a SQL Table

This is a method to add an Updated Timestamp to a SQL Table where one does not exist and the source application does not provide updated information as data is changed.

This would normally be implemented within the Application or perhaps SQL Triggers, however this is another approach with Data Sync via a Hash value stored on the row.

You might want to consider this if your Tables are large and you are syncing into something that is slow or on the internet i.e. Dynamics 365, where Data Sync Incremental sync would be faster. With a change timestamp you can select records that have been changed since a point in time and only sync those records.

The example below starts out with a simple Table, which is a copy of the Suppliers Table from the Northwind database. This does not include any tracking information.

CREATE TABLE [dbo].[MySuppliers] (
    [SupplierID] int NOT NULL PRIMARY KEY CLUSTERED,
    [CompanyName] nvarchar(40) NOT NULL,
    [ContactName] nvarchar(30) NULL,
    [ContactTitle] nvarchar(30) NULL,
    [Address] nvarchar(60) NULL,
    [City] nvarchar(15) NULL,
    [Region] nvarchar(15) NULL,
    [PostalCode] nvarchar(10) NULL,
    [Country] nvarchar(15) NULL,
    [Phone] nvarchar(24) NULL,
    [Fax] nvarchar(24) NULL,
    [HomePage] nvarchar(MAX) NULL
)
GO

Add Tracking Columns to Table​

We need to add two columns to hold the hash value and to hold the time of change that will be calculated in the next steps.

To do this we can run the script below. This can be done from the SQL Query Tool if you have saved your SQL connection to the connection library.

ALTER TABLE [dbo].[MySuppliers] ADD [Hash] nvarchar(50) NULL
GO
ALTER TABLE [dbo].[MySuppliers] ADD [Updated] datetime DEFAULT(GETUTCDATE()) NOT NULL

Create the Data Sync Project​

We now need to create and setup the Data Sync project. Open Data Sync and connect the Source and Target to the same SQL Table, in this example it is the MySuppliers table we created earlier.

Create the Tracking Columns​

Then create two calculated columns which will calculate the change tracking information. To do this click onto the calculated column button in the source window this will open the window where you can enter in the needed expression.

Calculated Column Button

1- MyHash​

Enter in a name for the column e.g. MyHash and then enter the expression needed.

This column will return a String MD5 hash of the values in the row and uses the MD5HASH and CONCAT functions with the syntax:

MD5HASH(CONCAT(columns))

Calculated Column

Click OK to create the column and then click onto a source column to de-select the MyHash column we just created.

2- MyUpdated​

Now click onto the calculated column button again and enter in a name, e.g. MyUpdated, change the data type to DateTime and enter the expression needed. This column will return a DateTime of the Change (when this project is run) using the UTCNOW function.

Calculated Column

Map the Columns & Set the Properties​

You then need to map these new calculated columns to the Hash and Updated columns in the Target.

Mapping

Set the Schema Properties​

Now we want to update the Time and Hash values only when the data has changed. To do this we set the TriggerUpdate property in the schema map to False for all columns except the calculated column MyHash.

Trigger Update Schema Map

Compare and Sync​

The first time we run the project all rows will be updated, but on the next run only the rows where the Hash value is different will the Updated value be changed. Click Run Compare to preview the changes and then click Synchronise and Start to sync the results.

When a record is changed the hash and updated values will show as an update item in the compare results. This could look something like the screen capture below:

Compare Results

To generate the change tracking, you would run this as a pre-step within either Ouvvi or the Run Tool and make sure your project is set to use Incremental Sync. The benefit of incremental sync being if you have a table of say 1 Million Rows in Dynamics 365, running this against SQL locally will ensure the subsequent Sync into Dynamics with Incremental mode is fast as only the changed rows need be loaded.