在临时表中使用旧日期播种数据-SQL Server [英] 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
)
我遇到以下错误.
无法将显式值插入到的GENERATED ALWAYS列中 表"DevDB.dbo.Contact".将INSERT与列列表一起使用以排除 GENERATED ALWAYS列,或将DEFAULT插入GENERATED ALWAYS 列.
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.
请协助我如何在此临时表
推荐答案
最后我找到了解决方法
步骤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
第3步:插入带有过去日期的必需记录
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
)
第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)
);
就这样...
这篇关于在临时表中使用旧日期播种数据-SQL Server的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!