通过

在系统版本控制时态表中修改数据

  • 项目

适用于：SQL Server 2016 (13.x) 及更高版本 Azure SQL 数据库 Azure SQL 托管实例

系统版本控制时态表中的数据使用常规数据操作语言 (DML) 语句进行修改，但有一个重要的区别：无法直接修改时间段列数据。 数据更新时，它就是版本控制的，每个已更新行的以前版本都将插入到历史记录表中。 当删除数据时，删除是合乎逻辑的，行将从当前表移入历史记录表中；数据不会被永久删除。

插入数据

插入新数据时，如果它们不是 HIDDEN，则需要对 PERIOD 列作出说明。 还可以对时态表使用分区切换。

使用可见的时间段列插入新数据

有如下可见的 INSERT 列用来说明 PERIOD 列时，则可以构建 PERIOD 语句：

如果在 INSERT 语句中指定列列表，则可以省略 PERIOD 列，因为系统将为这些列自动生成值。

-- Insert with column list and without period columns
INSERT INTO [dbo].[Department] (
      [DeptID],
      [DeptName],
      [ManagerID],
      [ParentDeptID]
)
VALUES (10, 'Marketing', 101, 1);

如果确实在 INSERT 语句中的列列表中指定了 PERIOD 列，那么需要指定 DEFAULT 作为它们的值。

INSERT INTO [dbo].[Department] (
   DeptID,
   DeptName,
   ManagerID,
   ParentDeptID,
   ValidFrom,
   ValidTo
)
VALUES (11, 'Sales', 101, 1, DEFAULT, DEFAULT);

如果未在 INSERT 语句中指定列列表，则为 DEFAULT 列指定 PERIOD。

-- Insert without a column list and DEFAULT values for period columns
INSERT INTO [dbo].[Department]
VALUES(12, 'Production', 101, 1, DEFAULT, DEFAULT);

将数据插入到具有 HIDDEN 时间段列的表中

如果将 PERIOD 列指定为 HIDDEN，则无需对 INSERT 语句中的 PERIOD 列作出说明。 此行为保证对从版本控制中受益的表启用系统版本控制时，旧版应用程序将继续工作。

CREATE TABLE [dbo].[CompanyLocation] (
    [LocID] [int] IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    [LocName] [varchar](50) NOT NULL,
    [City] [varchar](50) NOT NULL,
    [ValidFrom] [datetime2] GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
    [ValidTo] [datetime2] GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,
    PERIOD FOR SYSTEM_TIME([ValidFrom], [ValidTo])
)
WITH (SYSTEM_VERSIONING = ON);
GO

INSERT INTO [dbo].[CompanyLocation]
VALUES ('Headquarters', 'New York');

使用 PARTITION SWITCH 插入数据

如果当前表已分区，可以使用 PARTITION SWITCH 作为将数据加载到空分区或并行加载到多个分区的有效机制。

在具有时态表的 PARTITION SWITCH IN 语句中使用的临时表必须定义 SYSTEM_TIME PERIOD，但它不需要是时态表。 这可确保在数据插入到临时表期间或将 SYSTEM_TIME 时间段添加到预填充的临时表时，执行时态一致性检查。

/* Create staging table with period definition for SWITCH IN temporal table */
CREATE TABLE [dbo].[Staging_Department_Partition2] (
    [DeptID] [int] NOT NULL,
    [DeptName] [varchar](50) NOT NULL,
    [ManagerID] [int] NULL,
    [ParentDeptID] [int] NULL,
    [ValidFrom] [datetime2] GENERATED ALWAYS AS ROW START NOT NULL,
    [ValidTo] [datetime2] GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME([ValidFrom], [ValidTo])
) ON [PRIMARY]

/* Create aligned primary key */
ALTER TABLE [dbo].[Staging_Department_Partition2]
ADD CONSTRAINT [Staging_Department_Partition2_PK]
PRIMARY KEY CLUSTERED ([DeptID] ASC) ON [PRIMARY];

/*
Create and enforce constraints for partition boundaries.
Partition 2 contains rows with DeptID > 100 and DeptID <=200
*/
ALTER TABLE [dbo].[Staging_Department_Partition2]
WITH CHECK ADD CONSTRAINT [chk_staging_Department_partition_2] CHECK (
   [DeptID] > N'100'
   AND [DeptID] <= N'200'
);

ALTER TABLE [dbo].[Staging_Department_Partition2]
CHECK CONSTRAINT [chk_staging_Department_partition_2];

/*Load data into staging table*/
INSERT INTO [dbo].[staging_Department] (
    [DeptID],
    [DeptName],
    [ManagerID],
    [ParentDeptID]
    )
VALUES (101, 'D101', 1, NULL);

/*Use PARTITION SWITCH IN to efficiently add data to current table */
ALTER TABLE [Staging_Department]
SWITCH TO [dbo].[Department] PARTITION 2;

如果尝试在没有时间段定义的情况下从表中执行 PARTITION SWITCH，将收到错误消息：

Msg 13577, Level 16, State 1, Line 25 ALTER TABLE SWITCH statement failed on table 'MyDB.dbo.Staging_Department_2015_09_26' because target table has SYSTEM_TIME PERIOD while source table does not have it.

更新数据

使用常规的 UPDATE 语句更新当前表中的数据。 可以灾难情况根据历史记录表来更新当前表中的数据。 但是，无法更新 PERIOD 列，且当 SYSTEM_VERSIONING = ON 时不能直接在历史记录表中更新数据。

如果将 SYSTEM_VERSIONING 设置为 OFF，且从当前表和历史记录表中更新行，则系统不会保留更改的历史记录。

更新当前表

在此示例中，对于 ManagerID 为 DeptID 的每一行，都将更新 10 列。 PERIOD 列不会以任何方式引用。

UPDATE [dbo].[Department]
SET [ManagerID] = 501
WHERE [DeptID] = 10;

但是，不能更新 PERIOD 列且不能更新历史记录表。 在此示例中，尝试更新 PERIOD 列会生成错误。

UPDATE [dbo].[Department]
SET ValidFrom = '2015-09-23 23:48:31.2990175'
WHERE DeptID = 10;

语句可能会生成以下错误。

Msg 13537, Level 16, State 1, Line 3
Cannot update GENERATED ALWAYS columns in table 'TmpDev.dbo.Department'.

从历史记录表更新当前表

可以对当前表使用 UPDATE，以便在过去的特定时间点将实际行状态恢复为有效状态。 将此视为恢复为“上次已知完好的行版本”。 以下示例显示了恢复为截至 2015 年 4 月 25 日的历史记录表中的值，其中 DeptID 为 10。

UPDATE Department
SET DeptName = History.DeptName
FROM Department
FOR SYSTEM_TIME AS OF '2015-04-25' AS History
WHERE History.DeptID = 10
    AND Department.DeptID = 10;

删除数据

使用常规的 DELETE 语句删除当前表中的数据。 已删除行的结束时间段列将填充底层事务的开始时间。 SYSTEM_VERSIONING 为 ON 时，不能直接从历史记录表删除行。 如果设置 SYSTEM_VERSIONING = OFF，且从当前表和历史记录表中删除行，则系统不会保留更改的历史记录。

SYSTEM_VERSIONING = ON 时，不支持以下语句：

  • TRUNCATE
  • 用于当前表的 SWITCH PARTITION OUT
  • 用于历史记录表的 SWITCH PARTITION IN

使用 MERGE 在时态表中修改数据

对于 MERGE 列，INSERT 操作具有与 UPDATE 和 PERIOD 语句相同的限制。

CREATE TABLE DepartmentStaging (
    DeptId INT,
    DeptName VARCHAR(50)
);
GO

INSERT INTO DepartmentStaging
VALUES (1, 'Company Management');

INSERT INTO DepartmentStaging
VALUES (10, 'Science & Research');

INSERT INTO DepartmentStaging
VALUES (15, 'Process Management');

MERGE dbo.Department AS target
USING (
    SELECT DeptId, DeptName
    FROM DepartmentStaging
    ) AS source(DeptId, DeptName)
    ON (target.DeptId = source.DeptId)
WHEN MATCHED
    THEN UPDATE SET DeptName = source.DeptName
WHEN NOT MATCHED
    THEN
        INSERT (DeptId, DeptName)
        VALUES (source.DeptId, source.DeptName);

相关内容