Data sync between temporal tables in SQL Server

Question

I want to Sync data between two databases which have system-versioned(temporal) tables. The data sync option in Azure does not support temporal tables and I need to find another way of doing the sync between the databases.

I want to copy the data from one temporal table to another temporal table of another database using Azure data factory.Will Azure data factory support the data sync between temporal tables?

What will be the best way of syncing data between the two databases that have identical temporal tables?

Answer 1

I created a system-versioned(temporal) table in my Azure SQL database followed this document Creating a temporal table:

CREATE TABLE Department   
(    
     DeptID int NOT NULL PRIMARY KEY CLUSTERED  
   , DeptName varchar(50) NOT NULL  
   , ManagerID INT  NULL  
   , ParentDeptID int NULL  
   , SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL  
   , SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL  
   , PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)     
)   
WITH    
   (   
      SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.DepartmentHistory)   
   )   
;

I created two temporal tables Department and Department2 in different Azure SQL Server.

I test in the Data Factory copy active, choose the Department as source dataset, we can see the temporal table from the settings:

Choose the Department2 as link dataset, Table mapping::

Column mapping:

Active run succeeded:

That's mean Azure Data Factory support copy the data from one temporal table to another temporal table of another database.

Update:

Please reference this document: Temporal Tables in SQL Server 2016 – Part III.

Now, starting by INSERT, remember there are two datetime2 special columns in a temporal table declared as GENERATED ALWAYS AS ROW START / END. These are the PERIOD columns and they are mandatory but you cannot insert an explicit value into a GENERATED ALWAYS column. Its values will be automatically filled. When you insert a new row in the table, the “ROW START” column will have the value of SYSUTCDATETIME() (yes, don’t forget it’s UTC time!) and the “ROW END” column will have the value: ‘9999-12-31 23:59:59.9999999’ Basically when inserting a new row in a temporal table, regarding these two columns, you can use of the following options: 1. Use a column list and omit these two columns; 2. Use a column list without omitting these two columns and specify DEFAULT in the values list for each. 3. Don’t use a column list and specify DEFAULT in the values list for each.

Congratulations, the error has solved by yourself:

1. Loading the tables after switching off the versioning on the destination table and it worked.

ALTER TABLE [dbo].[Department] SET(SYSTEM_VERSIONING = OFF); ALTER TABLE [dbo].[Department] DROP PERIOD FOR SYSTEM_TIME;

2. Then after the copy active done, switch it back.

ALTER TABLE [dbo].[Department] ADD PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime); ALTER TABLE [dbo].[Department] SET(SYSTEM_VERSIONING = ON);

Hope this helps.