sqlsql-serversql-inserttemporaltemporal-database

Seed data with old dates in Temporal Table - SQL Server


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


Solution

  • 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...