代码之家 › 专栏 › 技术社区 › B.Balamanigandan

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

temporal-database temporal sql-insert sql-server sql

B.Balamanigandan · 技术社区 · 7 年前

我需要在下面的时间表中为我的本地开发目的播种数据,开始日期应该是旧的。给定的表架构是

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
)

无法在中的生成的ALWAYS列中插入显式值 GENERATED ALWAYS列,或在GENERATED ALWAYS中插入默认值柱

请帮助我如何将旧的数据添加或更新到此

4 回复 | 直到 7 年前

B.Balamanigandan 7 年前

最后我找到了解决办法

步骤#1 :需要切换关闭 SYSTEM_VERSIONING

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

步骤#2 :需要删除 PERIOD FOR SYSTEM_TIME

ALTER TABLE dbo.Contact DROP PERIOD FOR SYSTEM_TIME

:插入带有过去日期的必需记录

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
)

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

步骤#5 :需要切换这个 SYSTEM\u版本控制

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

Dmitrij Kultasev 7 年前

你想要实现什么?生成的ALWAYS列是技术列,如果将其设置为无法更新,则会自动更新。为了跟踪更改,您有ContactHistory表。

通常应使用以下插入:

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

在第一次插入后,这就是您所拥有的:

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

历史记录中还没有任何记录,因为它不存储实际记录。现在,如果要更改数据,请使用普通UPDATE语句,忽略这些生成的ALWAYS列:

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

从dbo中选择*。联系
从dbo中选择*。联系人历史记录

所以,如果您想获得SCD的本机支持,那么Sql Server会为您做一切,只是不要碰这些列。如果出于某些特殊原因需要更新这些列,那么就不要使用GENERATED ALWAYS列,而是使用默认约束。

Thomas Phaneuf 3 年前

将系统版本控制添加到表中。
收集数据以添加到系统版本表中,其中的列用于填充目标表中的system\u TIME列。请注意,SYSTEM_时间值将位于UTC时区,因此可能需要在“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));

在最初从现有表填充表之后,关于不修改SYSTEM\u时间日期或删除历史记录的所有限制在世界上都是有意义的。

希望这能帮助别人。

-1

Serg 7 年前

注意:仅引入Sql server 2016 System-Versioned Temporal Tables

要定义的表定义中的SQL/Foundation包含子句 “应用时间段表”(有效时间表), “系统版本化应用程序时间段表”(双时态表)。 https://en.wikipedia.org/wiki/Temporal_database