Seed data with old dates in Temporal Table - SQL Server

Question

I need to seed data for my local development purpose in the following Temporal Table, the start date should be old. The given Table Schema is

CREATE TABLE [dbo].[Contact](
    [ContactID] [uniqueidentifier] NOT NULL,
    [ContactNumber] [nvarchar](50) NOT NULL,
    [SequenceID] [int] IDENTITY(1,1) NOT NULL,
    [SysStartTime] [datetime2](0) GENERATED ALWAYS AS ROW START NOT NULL,
    [SysEndTime] [datetime2](0) GENERATED ALWAYS AS ROW END NOT NULL,
 CONSTRAINT [PK_Contact] PRIMARY KEY NONCLUSTERED 
(
    [ContactID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
    PERIOD FOR SYSTEM_TIME ([SysStartTime], [SysEndTime])
) ON [PRIMARY]
WITH
(
    SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[ContactHistory] , DATA_CONSISTENCY_CHECK = ON )
)

I need to Insert some old dated data into this table.

INSERT INTO dbo.Contact
(
    ContactID,
    ContactNumber,
    --SequenceID - this column value is auto-generated
    SysStartTime,
    SysEndTime
)
VALUES
(
    NEWID(), -- ContactID - uniqueidentifier
    N'9999912345', -- ContactNumber - nvarchar
    -- SequenceID - int
    '2017-09-01 06:26:59', -- SysStartTime - datetime2
    NULL -- SysEndTime - datetime2
)

I'm getting the following Error.

Cannot insert an explicit value into a GENERATED ALWAYS column in table 'DevDB.dbo.Contact'. Use INSERT with a column list to exclude the GENERATED ALWAYS column, or insert a DEFAULT into GENERATED ALWAYS column.

Kindly assist me how to add or Update a old dataed data into this Temporal Table

Answer 1

It can be done

It is possible to initialize your SQL Temporal (System-versioned) tables and history with existing data, including the dates. It just involves jumping through a bunch of silly hoops. Hopefully Microsoft will give us a better way to initialize these tables with existing historical data in the future.

The short version of the trick to putting existing data into these tables with specific SYSTEM_TIME dates is to:

1. Add system versioning to table.
2. Gather up data to add to system versioned table with columns to be used to populate the SYSTEM_TIME columns in the target tables. Note that the SYSTEM_TIME values will be in the UTC time zone, so it may be necessary to do something like AT TIME ZONE 'UTC' to get the dates right.
3. Turn off system versioning.
4. Create dynamic SQL for the CRUD operations.
5. Turn system versioning back on.

Here is a simplified sample of the process:

-- System versioned table: dbo.ManagerList
-- Primary Key: ManagerName
-- History table: dbo.ManagerList_History
-- SYSTEM_TIME columns: _PeriodStart, _PeriodEnd
-- Table with Data to Import: #SourceData

DECLARE @Script varchar(max)

-- Disable system versioning
ALTER TABLE dbo.ManagerList SET (SYSTEM_VERSIONING = OFF);

ALTER TABLE dbo.ManagerList 
DROP PERIOD FOR SYSTEM_TIME;

-- Prepare source data (in temporary table #SourceData)
UPDATE  A
SET     _PeriodEnd = B.PeriodEnd
FROM    #SourceData as A
        INNER JOIN 
        (
            SELECT  ManagerName,
                    _PeriodStart,
                    _PeriodEnd = 
                        LEAD(_PeriodStart, 1, datetime2fromparts(9999,12,31,23,59,59,9999999,7)) OVER
                        (
                            PARTITION BY ManagerName
                            ORDER BY _PeriodStart
                        )
        ) as B
            ON  A.ManagerName = B.ManagerName
            AND A._PeriodStart = B._PeriodStart

-- DELETE from System-Versioned table
DELETE  A
FROM    dbo.ManagerList as A
WHERE   NOT EXISTS
        (
            SELECT  1
            FROM    #SourceData
            WHERE   ManagerName = A.ManagerName
        )

-- UPDATE script for System-Versioned table
SET @Script = 
        'UPDATE A ' +
        'SET    FavoriteColor   = B.FavoriteColor, ' + 
        '       _PeriodStart    = B._PeriodStart ' +
        'FROM   dbo.ManagerList as A ' +
        '       INNER JOIN #SourceData as B ' + 
        '           ON A.ManagerName = B.ManagerName ' + 
        'WHERE  B._PeriodEnd > datefromparts(9999,12,31) ' + 
        '       ( ' +
        '           A.ManagerName       != B.ManagerName ' +
        '           OR A._PeriodStart   != B.PeriodStart ' +
        '       )'
EXEC (@Script)
    
-- UPDATE script for System-Versioned table
SET @Script = 
        'UPDATE A ' +
        'SET    FavoriteColor       = B.FavoriteColor, ' + 
        '       _PeriodStart    = B._PeriodStart ' +
        'FROM   dbo.ManagerList ' +
        '       INNER JOIN #SourceData as B ' + 
        '           ON A.ManagerName = B.ManagerName ' + 
        'WHERE  B._PeriodEnd > datefromparts(9999,12,31) ' + 
        '   AND (' +
        '           A.FavoriteColor     != B.FavoriteColor ' +
        '           OR A._PeriodStart   != B.PeriodStart ' +
        '       )'
EXEC (@Script)

-- INSERT script for System-Versioned table
SET @Script = 
        'INSERT dbo.ManagerList ' + 
        '( ' +
        '   ManagerName, ' +
        '   FavoriteColor, ' +
        '   _PeriodStart ' +
        ') ' +
        'SELECT ManagerName, ' +
        '       FavoriteColor, ' +
        '       _PeriodStart ' +
        'FROM   #SourceData as A ' +
        'WHERE  _PeriodEnd > datefromparts(9999,12,31) ' + 
        '   AND NOT EXISTS ' +
        '       ( ' +
        '           SELECT  1 ' +
        '           FROM    dbo.ManagerList ' +
        '           WHERE   ManagerName = A.ManagerName ' +
        '       )'
EXEC (@Script)

-- DELETE script for History table
SET @Script = 
        'DELETE A ' +
        'FROM   dbo.ManagerList_History as A ' +
        'WHERE  NOT EXISTS ' +
        '       ( ' +
        '           SELECT  1 ' +
        '           FROM    #SourceData ' +
        '           WHERE   ManagerName = A.ManagerName ' +
        '               AND _PeriodEnd < datefromparts(9999,12,31) ' +
        '       )'
EXEC (@Script)

-- UPDATE script for History table
SET @Script = 
        'UPDATE A ' +
        'SET    FavoriteColor   = B.FavoriteColor ' +
        'FROM   dbo.ManagerList_History as A' +
        '       INNER JOIN #SourceData as B ' + 
        '           ON  A.ManagerName = B.ManagerName ' + 
        '           AND A._PeriodStart = B._PeriodStart ' + 
        'WHERE  B._PeriodEnd < datefromparts(9999,12,31) ' + 
        '   AND (' +
        '           A.FavoriteColor     != B.FavoriteColor' +
        '       )'
EXEC (@Script)
    
-- INSERT script for History table
SET @Script = 
        'INSERT dbo.ManagerList ' + 
        '( ' +
        '   ManagerName, ' +
        '   FavoriteColor, ' +
        '   _PeriodStart ' +
        ') ' +
        'SELECT ManagerName, ' +
        '       FavoriteColor, ' +
        '       _PeriodStart ' +
        'FROM   #SourceData as A ' +
        'WHERE  _PeriodEnd < datefromparts(9999,12,31) ' + 
        '   AND NOT EXISTS ' +
        '       ( ' +
        '           SELECT  1 ' +
        '           FROM    dbo.ManagerList_History ' +
        '           WHERE   ManagerName = A.ManagerName ' +
        '               AND _PeriodStart = A._PeriodStart ' + 
        '       )'
EXEC (@Script)

-- Re-enabling system versioning
-- Note: Making this dynamic SQL solves compiler error
SET @Script = 
    'ALTER TABLE dbo.ManagerList ' +
    'ADD    PERIOD FOR SYSTEM_TIME (_PeriodStart, _PeriodEnd)';
EXEC (@Script)

ALTER TABLE dbo.ManagerList 
SET (SYSTEM_VERSIONING = ON  (HISTORY_TABLE = dbo.ManagerList_History));

The main problem seems to be that the compiler does not always recognize when things are turned off on the system versioned tables and will not let the non-dynamic SQL compile or run. Don't know why it is possible to do with dynamic SQL, but I am very glad it does.

After the tables are initially populated from existing, all the restrictions about not modifying the SYSTEM_TIME dates or deleting records in the history make all the sense in the world.

Hope this helps somebody out.

Answer 2

Finally I found a solution

Step #1: Need to Switch it OFF the SYSTEM_VERSIONING

ALTER TABLE dbo.Contact SET (SYSTEM_VERSIONING = OFF);

Step #2: Need to drop the PERIOD FOR SYSTEM_TIME

ALTER TABLE dbo.Contact DROP PERIOD FOR SYSTEM_TIME

Step #3: Insert required record with past date

INSERT INTO dbo.Contact
(
    ContactID,
    ContactNumber,
    SysStartTime,
    SysEndTime
)
VALUES
(
    NEWID(), -- ContactID - uniqueidentifier
    N'1234567890', -- ContactNumber - nvarchar
    '2014-09-13 00:00:00', -- SysStartTime - datetime2
    '9999-12-31 23:59:59' -- SysEndTime - datetime2
)

Step #4: Need to Add the PERIOD FOR SYSTEM_TIME

ALTER TABLE dbo.Contact
ADD PERIOD FOR SYSTEM_TIME ([SysStartTime], [SysEndTime])

Step #5: Need to Switch it ON the SYSTEM_VERSIONING

ALTER TABLE dbo.[Contact] SET (SYSTEM_VERSIONING = ON
 (HISTORY_TABLE=dbo.[ContactHistory],DATA_CONSISTENCY_CHECK=ON)
);

That's it...