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
2024-06-06 04:30:07.7427336
9999-12-31 23:59:59.9999999

2
zzzz
46
null
2024-06-06 04:30:07.7578416
9999-12-31 23:59:59.9999999




DeptID
DeptName
ManagerID
ParentDeptID
ValidFrom
ValidTo


2
bbb
46
null
2024-06-06 04:30:07.7578416
2024-06-06 04:30:07.7578416

3
ccc
77
null
2024-06-06 04:30:07.7578416
2024-06-06 04:30:07.7578416


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
2024-06-06 04:30:07.7427336
9999-12-31 23:59:59.9999999

2
zzzz
46
null
2024-06-06 04:30:07.7578416
9999-12-31 23:59:59.9999999




DeptID
DeptName
ManagerID
ParentDeptID
ValidFrom
ValidTo


2
bbb
46
null
2024-06-06 04:30:07.7578416
2024-06-06 04:30:07.7578416

3
ccc
77
null
2024-06-06 04:30:07.7578416
2024-06-06 04:30:07.7578416

DeptID	DeptName	ManagerID	ParentDeptID	ValidFrom	ValidTo
1	aaa	23	null	2024-06-06 04:30:07.7427336	9999-12-31 23:59:59.9999999
2	zzzz	46	null	2024-06-06 04:30:07.7578416	9999-12-31 23:59:59.9999999