Advertisement
Guest User

Untitled

a guest
Sep 29th, 2023
123
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.47 KB | None | 0 0
  1. CREATE DATABASE TestCDC;
  2. GO
  3. USE TestCDC;
  4. GO
  5. EXEC sys.sp_cdc_enable_db;
  6. GO
  7. CREATE TABLE dbo.Temp
  8. (KeyCol int NOT NULL PRIMARY KEY,
  9. DataCol int NOT NULL UNIQUE);
  10. GO
  11. INSERT INTO dbo.Temp (KeyCol, DataCol)
  12. SELECT gs.value, gs.value
  13. FROM GENERATE_SERIES(1, 500) AS gs;
  14. GO
  15. EXEC sys.sp_cdc_enable_table @source_schema = N'dbo',
  16. @source_name = N'Temp',
  17. @role_name = NULL,
  18. @supports_net_changes = 1;
  19. GO
  20.  
  21. -- UPDATE, tracked as before update/after update
  22. UPDATE dbo.Temp
  23. SET DataCol = 1234
  24. WHERE KeyCol = 123;
  25.  
  26. -- MERGE, tracked as before update/after update
  27. MERGE INTO dbo.Temp
  28. USING (SELECT 123, 1235) AS Src (a, b)
  29. ON Src.a = KeyCol
  30. WHEN MATCHED THEN UPDATE SET DataCol = Src.b;
  31.  
  32. -- UPDATE, tracked as delete/insert
  33. WITH Src AS
  34. (SELECT * FROM Temp WHERE KeyCol <> 123
  35. UNION ALL SELECT 123, 1236)
  36. UPDATE dbo.Temp
  37. SET DataCol = Src.DataCol
  38. FROM Src
  39. WHERE Src.KeyCol = Temp.KeyCol;
  40.  
  41. -- MERGE, tracked as delete/insert
  42. WITH Src AS
  43. (SELECT * FROM Temp WHERE KeyCol <> 123
  44. UNION ALL SELECT 123, 1237)
  45. MERGE INTO dbo.Temp
  46. USING Src
  47. ON Src.KeyCol = Temp.KeyCol
  48. WHEN MATCHED THEN UPDATE SET DataCol = Src.DataCol;
  49.  
  50. WAITFOR DELAY '0:00:10';
  51. -- View tracked changes
  52. SELECT *
  53. FROM cdc.dbo_Temp_CT
  54. WHERE KeyCol = 123;
  55. GO
  56.  
  57. EXEC sys.sp_cdc_disable_db;
  58. GO
  59. USE tempdb;
  60. ALTER DATABASE TestCDC SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
  61. DROP DATABASE TestCDC;
  62. GO
  63.  
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement