Replication of MS SQL tables that include computed columns

OritA — Fri, 22 Oct 2021 12:36:53 GMT

When running a Replicate task with MS SQL source endpoint, replication of computed columns is supported for Full Load only. This limitation is also mentioned in the Replicate User Guide:

“"Replication of computed columns is supported in Full Load replication tasks only. During change processing, INSERTs to computed columns will appear as NULL on the target and UPDATEs will be ignored."

If you have a table with computed column and you would still like that the value of the computed column will be replicated to the target you can do one of the following:

Option 1 :

In the Replicate UI under task design --> Table settings (of the table with the computed column) --> transform

Remove the computed column from the output columns list
Add a new column (with the name that you wish to be created in the target) and define the desired computation expression (should be similar to the computation expression defined for the computed column in the source endpoint)

Note : with this setting Replicate will do the required computation (instead of the db).

Option 2:

In the target endpoint create the table DDL with the computed column expression manually
In the Replicate UI under task setting --> Full Load --> Full Load Settings --> If target table already exists: set the option to ‘TRUNCATE before loading’

Note: this setting affects all task tables. i.e. if you choose this option you would have to create by yourself, in the target endpoint, the DDL of all tables included in the task.

If you wish, you can set Replicate to suspend tables with computed columns that are included in CDC tasks. This can be done by setting the internal parameter suspendTableWithComputedColumn to True (under the MS SQL source endpoint à advanced tab à internal parameters)

article Replication of MS SQL tables that include computed columns in Official Support Articles

Replication of MS SQL tables that include computed columns