tdmitch

UPDATE dbo.Emp
SET MiddleName = 'Muriel'
WHERE FirstName = 'Chandler'
AND LastName = 'Bing'

UPDATE dbo.Emp
SET LastName = 'Bing'
WHERE FirstName = 'Monica'
AND LastName = 'Geller'

tdmitch

SELECT ct.ID
, e.FirstName
, e.MiddleName
, e.LastName
, ct.SYS_CHANGE_OPERATION [Operation]
FROM CHANGETABLE(CHANGES dbo.Emp, @StartVersionID) ct
LEFT JOIN dbo.Emp e
ON e.ID = ct.ID
WHERE (SELECT MAX(v) FROM (VALUES(ct.SYS_CHANGE_VERSION), (ct.SYS_CHANGE_CREATION_VERSION)) AS VALUE(v)) <= @EndVersionID

tdmitch

DECLARE @StartVersionID BIGINT
, @EndVersionID BIGINT

-- Set the starting version ID
SET @StartVersionID = (SELECT Change_Tracking_Version
FROM etl.Change_Tracking_Version
WHERE Table_Name = 'Emp')
-- Set the ending version ID
SET @EndVersionID = CHANGE_TRACKING_CURRENT_VERSION()

tdmitch

MERGE etl.Change_Tracking_Version AS target
USING (SELECT 'Emp' [Table_Name]
, CHANGE_TRACKING_CURRENT_VERSION() [Version_ID] — Get the change tracking version ID
) AS source
ON target.Table_Name = source.Table_Name

WHEN MATCHED — Exists? Then update
THEN UPDATE
SET target.Change_Tracking_Version = source.Version_ID

tdmitch

CREATE TABLE Emp
(
ID INT PRIMARY KEY IDENTITY(1, 1)
, FirstName VARCHAR(100)
, MiddleName VARCHAR(100)
, LastName VARCHAR(100)
)

ALTER TABLE dbo.Emp ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON)

tdmitch

CREATE TABLE Emp
(
ID INT PRIMARY KEY IDENTITY(1, 1)
, FirstName VARCHAR(100)
, MiddleName VARCHAR(100)
, LastName VARCHAR(100)
)

ALTER TABLE dbo.Emp ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON)

tdmitch

CREATE SCHEMA [etl]

/*
User-defined table to store the most recent change tracking version ID
processed via ETL.
*/
CREATE TABLE [etl].[Change_Tracking_Version]
(
[Table_Name] VARCHAR(100) NOT NULL PRIMARY KEY
, [Change_Tracking_Version] [BIGINT] NULL

tdmitch

/* Get local time */
DECLARE @dt2 DATETIMEOFFSET = GETDATE() AT TIME ZONE 'Central Standard Time'
/* Convert to UTC */
SET @dt2 = @dt2 AT TIME ZONE 'UTC';  

/* Now query the table using the offset datetime2 */
SELECT ValidFrom, ValidTo, * FROM dbo.Customers
FOR SYSTEM_TIME AS OF @dt2
WHERE (address = '' OR customerid = 13)

tdmitch

SELECT *
FROM dbo.Customers
FOR SYSTEM_TIME AS OF '2017-10-10 19:37:02.2280376'
WHERE PostalCityID = 32887

tdmitch

SELECT *
FROM dbo.Customers
WHERE PostalCityID = 32887