Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement