In
SQL Server, Temporal Table is used to keep the full history of data change for
table without doing any extra coding or efforts. It will allow to you get table
data at any point of time of past. For more information about SQL Server
temporal tables, please visit temporal
table in SQL Server .
If
you want to modify (add or modify or delete column) the existing temporal table
schema, you need to make sure history table schema should be sync.
Here
are steps how to add new column ‘ZipCode’
in system-version temporal table.
1.
Turn off System Versioning
ALTER TABLE [dbo].[Office] SET (SYSTEM_VERSIONING = OFF);
2.
Modify the existing both Tables schema ( Office and OfficeHistory )
ALTER TABLE [dbo].[Office] ADD ZipCode varchar(10) NULL;
ALTER TABLE [dbo].[OfficeHistory] ADD ZipCode varchar(10) NULL;
3.
If data update is needed in history table, do it
Update [dbo].[OfficeHistory]
SET ZipCode = '43212' where City = 'NYK'
4.
Turn on System Version option
ALTER TABLE [dbo].[Office]
SET
(
SYSTEM_VERSIONING = ON
( HISTORY_TABLE = [dbo].[OfficeHistory])
);
Tables:
CREATE TABLE dbo.OfficeHistory(
OfficeID int NOT NULL
, OfficeName varchar(50) NOT NULL
, Street varchar(50) NOT NULL
, City varchar(50) NOT NULL
, State varchar(2) NOT NULL
,SysStartTime datetime2
NOT NULL,
,SysEndTime datetime2
NOT NULL,
)
CREATE TABLE Office
(
OfficeID int NOT NULL PRIMARY KEY CLUSTERED
, OfficeName varchar(50) NOT NULL
, Street varchar(50) NOT NULL
, City varchar(50) NOT NULL
, State varchar(2) NOT NULL
, SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL
, SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL
, PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)
)
WITH
(
SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.OfficeHistory)
)
Other
blogs related to Temporal Table:
No comments:
Post a Comment