CREATE DATABASE TestCDC;
GO
USE TestCDC;
GO
EXEC sys.sp_cdc_enable_db;
GO
CREATE TABLE dbo.Temp
    (KeyCol  int NOT NULL PRIMARY KEY,
     DataCol int NOT NULL UNIQUE);
GO
INSERT INTO dbo.Temp (KeyCol, DataCol)
SELECT gs.value, gs.value
FROM   GENERATE_SERIES(1, 500) AS gs;
GO
EXEC sys.sp_cdc_enable_table @source_schema = N'dbo',
                             @source_name = N'Temp',
                             @role_name = NULL,
                             @supports_net_changes = 1;
GO

-- UPDATE, tracked as before update/after update
UPDATE dbo.Temp
SET    DataCol = 1234
WHERE  KeyCol = 123;

-- MERGE, tracked as before update/after update
MERGE INTO dbo.Temp
USING (SELECT 123, 1235) AS Src (a, b)
ON Src.a = KeyCol
WHEN MATCHED THEN UPDATE SET DataCol = Src.b;

-- UPDATE, tracked as delete/insert
WITH Src AS
(SELECT * FROM Temp WHERE KeyCol <> 123
UNION ALL SELECT 123, 1236)
UPDATE dbo.Temp
SET DataCol = Src.DataCol
FROM Src
WHERE Src.KeyCol = Temp.KeyCol;

-- MERGE, tracked as delete/insert
WITH Src AS
(SELECT * FROM Temp WHERE KeyCol <> 123
UNION ALL SELECT 123, 1237)
MERGE INTO dbo.Temp
USING Src
ON Src.KeyCol = Temp.KeyCol
WHEN MATCHED THEN UPDATE SET DataCol = Src.DataCol;

WAITFOR DELAY '0:00:10';
-- View tracked changes
SELECT *
FROM   cdc.dbo_Temp_CT
WHERE  KeyCol = 123;
GO

EXEC sys.sp_cdc_disable_db;
GO
USE tempdb;
ALTER DATABASE TestCDC SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE TestCDC;
GO