在时态表中使用旧日期为数据设定种子

问：

我需要在以下时态表中为我的本地开发目的设定数据种子，开始日期应该是旧的。给定的表架构是

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

我需要在此表中插入一些旧的过时数据。

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
)

我收到以下错误。

无法将显式值插入到 GENERATED ALWAYS 列中表“DevDB.dbo.Contact”。将 INSERT 与列列表一起使用以排除 GENERATED ALWAYS 列，或将 DEFAULT 插入到 GENERATED ALWAYS 中列。

请协助我如何将旧的数据添加或更新到此时态表中

SQL Server SQL 插入时态数据库

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
)

步骤#4：需要添加PERIOD FOR SYSTEM_TIME

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

步骤#5：需要打开它SYSTEM_VERSIONING

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

就是这样。。。

尝试重新启用SYSTEM_VERSIONING时，我收到一个错误，说“ADD PERIOD FOR SYSTEM_TIME failed because table '<myTable>' contains records where end of period is not equal to MAX datetime”，但我通过将的值设置为 but 来解决它，这可能是的实际最大值？我正在运行 SqlServer 2017，fwiwSysEndTime'9999-12-31 23:59:59.9999999'DATETIME2

0赞 Thomas Phaneuf 1/4/2021 #2

这是可以做到的

可以使用现有数据（包括日期）初始化 SQL Temporal （System-versioned）表和历史记录。它只是涉及跳过一堆愚蠢的箍。希望 Microsoft 将来能给我们一种更好的方法来使用现有的历史数据初始化这些表。

将现有数据放入具有特定SYSTEM_TIME日期的这些表中的诀窍的简短版本是：

将系统版本控制添加到表中。
收集要添加到系统版本化表的数据，其中包含用于填充目标表中SYSTEM_TIME列的列。请注意，SYSTEM_TIME值将采用 UTC 时区，因此可能需要执行类似 AT TIME ZONE 'UTC' 的操作来获得正确的日期。
关闭系统版本控制。
为 CRUD 操作创建动态 SQL。
重新打开系统版本控制。

以下是该过程的简化示例：

-- 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));

主要问题似乎是编译器并不总是识别系统版本控制表上何时关闭，并且不会让非动态 SQL 编译或运行。不知道为什么可以使用动态 SQL，但我很高兴它做到了。

在最初从现有表填充表后，有关不修改SYSTEM_TIME日期或删除历史记录的所有限制在世界上都有意义。

希望这对某人有所帮助。

上一个：配对两列的每个组合，并计算 data.table 中第三列的总和

下一个：如何在 angularJS 控制器中编写 Switch 语句

在时态表中使用旧日期为数据设定种子 - SQL Server

Seed data with old dates in Temporal Table - SQL Server

评论

评论

这是可以做到的