SQL Server: How to Move Temporal Table from One Database to Another Database

Article
1/17/2024

Introduction

Temporal tables (also known as system-versioned temporal tables) was introduced in SQL Server 2016, which is a new built-in feature to support storing a full history of data changes and to easily analysing data changes. This feature can be used for the auditing purpose.

If you want to move a temporal table from one database to another, you cannot easily export data to the new temporal table due to its versioning history table. This article will demonstrate how to move a temporal table from one database to another in SQL server.

Source Sample Temporal Table

Let’s first create a source test database TestDB1.

-- Create a source test database TestDB1
USE [master];
GO
CREATE DATABASE  [TestDB1];
GO

And then create a sample temporal table Customer.

-- Create a temporal table Customer
USE [TestDB1];
GO
CREATE TABLE  [dbo].[Customer] (
    [CustomerId] int  CONSTRAINT [PK_Customer] PRIMARY KEY  CLUSTERED NOT  NULL,
    [CustomerName] varchar(32) NOT NULL,
    [Address] varchar(128) NOT NULL,
    [City] varchar(64) NOT NULL,
    [State] varchar(32) NOT NULL,
    [IsActive] bit  CONSTRAINT [DF_Customer_IsActive] DEFAULT (1) NOT  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].[CustomerHistory], DATA_CONSISTENCY_CHECK = ON));
GO

You can see the temporal table Customer on SSMS.

We need to insert some sample data to the table Customer.

-- Insert three rows of sample data
USE [TestDB1];
GO
INSERT INTO  [dbo].[Customer] ([CustomerId], [CustomerName], [Address], [City], [State])  VALUES
(1, 'Customer01', 'Address01',  'New York', 'New York'), 
(2, 'Customer02', 'Address02',  'Jersey', 'New Jersey'), 
(3, 'Customer03', 'Address02',  'Miami', 'Florida');
GO

Let's select data from both Customer and CustomerHistory tables.

-- List data
USE [TestDB1];
GO
SELECT * FROM [dbo].[Customer];
SELECT * FROM [dbo].[CustomerHistory];
GO

You can see there are three rows of data in the Customer table but nothing in the CustomerHistory table since no data has been changed yet.

We need to update some data so that data changes will appear in the CustomerHistory table.

-- Update some data
USE [TestDB1];
GO
UPDATE [dbo].[Customer] SET [IsActive] = 0 WHERE [CustomerId] = 3;
UPDATE [dbo].[Customer] SET [Address] = 'NewAddress3' WHERE  [CustomerId] = 2;
GO

Select data from both tables again.

-- List data
USE [TestDB1];
GO
SELECT * FROM [dbo].[Customer];
SELECT * FROM [dbo].[CustomerHistory];
GO

This time you should see two rows of data in the CustomerHistory table.

Destination Sample Tables

Now we need to prepare the tables in the destination database. First we create a destination database TestDB2.

-- Create a source test database TestDB2
USE [master];
GO
CREATE DATABASE  [TestDB2];
GO

And then we need to create both Customer and CustomerHistory tables. Please note that the history table must have the same columns and the same data types as those in the Customer table, but you do not need any default constraints in the history table.

-- Create both Customer and CustomerHistory tablesUSE [TestDB2];
GO
CREATE TABLE  [dbo].[Customer] (
    [CustomerId] int  CONSTRAINT [PK_Customer] PRIMARY KEY  CLUSTERED NOT  NULL,
    [CustomerName] varchar(32) NOT NULL,
    [Address] varchar(128) NOT NULL,
    [City] varchar(64) NOT NULL,
    [State] varchar(32) NOT NULL,
    [IsActive] bit  CONSTRAINT [DF_Customer_IsActive] DEFAULT (1) NOT  NULL,
    [SysStartTime] datetime2 NOT NULL,
    [SysEndTime] datetime2 NOT NULL
);
GO
CREATE TABLE  [dbo].[CustomerHistory] (
    [CustomerId] int  NOT NULL,
    [CustomerName] varchar(32) NOT NULL,
    [Address] varchar(128) NOT NULL,
    [City] varchar(64) NOT NULL,
    [State] varchar(32) NOT NULL,
    [IsActive] bit  NOT NULL,
    [SysStartTime] datetime2 NOT NULL,
    [SysEndTime] datetime2 NOT NULL
);
GO

You can see both tables of Customer and CustomerHistory on SSMS.

Export Data from Temporal Table in Source Database to Tables in Destination Database

You can use the SQL Server Export Data wizard to export data of the temporal table Customer and its history table CustomerHistory from the source database TestDB1 to the same tables in the destination database TestDB2. For this demo, we use the T-SQL script to copy data.

-- T-SQL to copy data from source database to destination data
USE [TestDB2];
GO
INSERT INTO  [dbo].[Customer]
SELECT * FROM [TestDB1].[dbo].[Customer];
GO
INSERT INTO  [dbo].[CustomerHistory]
SELECT * FROM [TestDB1].[dbo].[CustomerHistory];
GO

Convert Normal Tables to Temporal Table

Now we can convert the normal tables Customer and CustomerHistory to the temporal table using T-SQL script.

-- Convert normal tables to temporal table
USE [TestDB2];
GO
-- Add both datetime2 columns to period for system time
ALTER TABLE  [dbo].[Customer]
ADD PERIOD FOR SYSTEM_TIME ([SysStartTime], [SysEndTime]);
-- Turn on system versioning
ALTER TABLE  [dbo].[Customer]
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[CustomerHistory], DATA_CONSISTENCY_CHECK = ON));
GO

Now we can see that the Customer table is the temporal table.

Let's select data from both tables.

-- List data
USE [TestDB2];
GO
SELECT * FROM [dbo].[Customer];
SELECT * FROM [dbo].[CustomerHistory];
GO

We can see that the outputs are the same as the ones from the source database.

Drop Source Temporal Table

Finally you can drop the temporal table Customer from the source database TestDB1 if you want. You can not simply use the DROP TABLE statement to drop the temporal table. That will throw an error.

USE [TestDB1];
GO
-- Try to drop temporal table
DROP TABLE  IF EXISTS [dbo].[Customer];
GO

Here is the error you will get

Msg 13552, Level 16, State 1, Line 122

Drop table operation failed on table 'TestDB1.dbo.Customer' because it is not supported operation on system-versioned temporal tables.

So we need first to set the system versioning off and then to drop both Customer and CustomerHistory tables.

-- Delete a temporal table
USE [TestDB1];
GO
-- Set SYSTEM_VERSIONING off
ALTER TABLE  [dbo].[Customer] SET  (SYSTEM_VERSIONING = OFF);
-- Drop history table
DROP TABLE  IF EXISTS [dbo].[CustomerHistory];
-- Drop table
DROP TABLE  IF EXISTS [dbo].[Customer];
GO

The temporal table Customer was deleted.

Share via