SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select @@version;


(No column name)


Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64) 
 Oct  8 2022 05:58:25 
 Copyright (C) 2022 Microsoft Corporation
 Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)



CREATE TABLE Department
(
    DeptID INT NOT NULL PRIMARY KEY CLUSTERED,
    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)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.DepartmentHistory));
select * from Department
select * from DepartmentHistory


DeptID
DeptName
ManagerID
ParentDeptID
ValidFrom
ValidTo




DeptID
DeptName
ManagerID
ParentDeptID
ValidFrom
ValidTo


insert into Department (DeptID, DeptName, ManagerId) VALUES (1, 'aaa', 23)
insert into Department (DeptID, DeptName, ManagerId) VALUES (2, 'bbb', 46)
insert into Department (DeptID, DeptName, ManagerId) VALUES (3, 'ccc', 77)

update Department set DeptName = 'zzzz' where DeptID = 2

delete from Department where DeptID = 3
5 rows affected

select * from Department
select * from DepartmentHistory


DeptID
DeptName
ManagerID
ParentDeptID
ValidFrom
ValidTo


1
aaa
23
null
2025-04-04 23:05:58.9528862
9999-12-31 23:59:59.9999999

2
zzzz
46
null
2025-04-04 23:05:58.9528862
9999-12-31 23:59:59.9999999




DeptID
DeptName
ManagerID
ParentDeptID
ValidFrom
ValidTo


2
bbb
46
null
2025-04-04 23:05:58.9528862
2025-04-04 23:05:58.9528862

3
ccc
77
null
2025-04-04 23:05:58.9528862
2025-04-04 23:05:58.9528862


select * INTO Department_COPY  from Department
select * INTO DepartmentHistory_COPY from DepartmentHistory
4 rows affected

select * from Department_COPY
select * from DepartmentHistory_COPY


DeptID
DeptName
ManagerID
ParentDeptID
ValidFrom
ValidTo


1
aaa
23
null
2025-04-04 23:05:58.9528862
9999-12-31 23:59:59.9999999

2
zzzz
46
null
2025-04-04 23:05:58.9528862
9999-12-31 23:59:59.9999999




DeptID
DeptName
ManagerID
ParentDeptID
ValidFrom
ValidTo


2
bbb
46
null
2025-04-04 23:05:58.9528862
2025-04-04 23:05:58.9528862

3
ccc
77
null
2025-04-04 23:05:58.9528862
2025-04-04 23:05:58.9528862

DeptID	DeptName	ManagerID	ParentDeptID	ValidFrom	ValidTo
1	aaa	23	null	2025-04-04 23:05:58.9528862	9999-12-31 23:59:59.9999999
2	zzzz	46	null	2025-04-04 23:05:58.9528862	9999-12-31 23:59:59.9999999