Seed data with old dates in Temporal Table - SQL S

问题:

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

回答1:

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

回答2:

What are you trying to achieve? The GENERATED ALWAYS columns are technical columns and if you set them as that you can't update them, they are updated automatically. For tracking changes you have ContactHistory table.

Normally you are supposed to use following INSERTs:

INSERT INTO dbo.Contact
(
    ContactID, --NEWID()
    ContactNumber
)
VALUES
(
    '045ABA61-1C64-4FE4-B079-18A9A50335D5', -- ContactID - uniqueidentifier
    N'9999912345'
);

Then after the first insert this is what you have:

select * from dbo.Contact
select * from dbo.ContactHistory

You do not have any records in history yet as it doesn't store the actual record. Now if you would like to change the data, then you use normal UPDATE statements ignoring these GENERATED ALWAYS columns:

UPDATE dbo.Contact SET ContactNumber = '123456789' WHERE ContactId = '045ABA61-1C64-4FE4-B079-18A9A50335D5'

Let's check the data again:

select * from dbo.Contact
select * from dbo.ContactHistory

Now you have a bit different situation:

As you can see that the actual data is updated as expected and the history table has the old record with the "closed" EndTime.

So, if you would like to have native support of the SCD then Sql Server does everything for, just do not touch these columns. If for the some special reasons you need to update these columns, then just do not use GENERATED ALWAYS columns and use DEFAULT constrains for that.

回答3:

Note Sql server 2016 introduced only System-Versioned Temporal Tables from ANSI Standard 2011 which defines 3 types of temporal tables

As of December 2011, ISO/IEC 9075, Database Language SQL:2011 Part 2: SQL/Foundation included clauses in table definitions to define "application-time period tables" (valid time tables), "system-versioned tables" (transaction time tables) and "system-versioned application-time period tables" (bitemporal tables). https://en.wikipedia.org/wiki/Temporal_database

System-Versioned Temporal Tables do not support changes in the already registered history by design. So to create a test temporal data set you need to re-execute all hystory INSERTs in an order.

回答4:

You can not insert old data timestamp into the temporal table directly. The values SysStartDate and SysEndDate are updated automatically as you work with the table. One possible workaround is to insert test data as you do:

INSERT INTO dbo.Contact
(
    ContactID,
    ContactNumber,
)
VALUES
(
    NEWID(), -- ContactID - uniqueidentifier
    N'9999912345', -- ContactNumber - nvarchar
)

and then iteratively process UPDATES

DECLARE @i int = 0 -- counter

WHILE @i < 30
BEGIN
   WAITFOR DELAY '00:00:10' -- hh:mi:ss, wait for 10 seconds

   -- perform update with random data

   SET @i = @i + 1
END