CDC | Change Data Capture | SQL Incremental Load |

Welcome to Praasy Technologies
This video session explains and provides a full demo about
1) What is CDC - Change Data Capture?
S_C_R_I_P_T_S
--STEP 1 Create DB
CREATE DATABASE DB_CDC
SELECT NAME, IS_CDC_ENABLED FROM SYS.DATABASES WHERE NAME = 'DB_CDC' --0
--STEP 2 Enable CDC on DB
USE DB_CDC
EXEC SP_CHANGEDBOWNER 'SA';
EXEC SYS.SP_CDC_ENABLE_DB
--STEP 3
CREATE TABLE Employees(Id INT IDENTITY PRIMARY KEY, Name VARCHAR(100),Designation VARCHAR(100),Salary DECIMAL)
INSERT INTO Employees VALUES
('User1', 'Software Engineer', 150000),
('User2', 'Quality Assuarance Engineer', 120000),
('USer3', 'Business Analyst', 250000),
('USer4', 'Systems Engineer', 150000),
('USer5', 'Project Manager', 200000)
SELECT * FROM EMPLOYEES
--STEP 4 ENABLE CDC ON TABLE
EXEC SYS.SP_CDC_ENABLE_TABLE
@SOURCE_SCHEMA = N'DBO',
@SOURCE_NAME = N'EMPLOYEES',
@ROLE_NAME = N'USERROLE',
@SUPPORTS_NET_CHANGES = 1
select is_tracked_by_cdc, * from sys.tables where name='Employees'
--STEP 5
SELECT * FROM EMPLOYEES
SELECT * FROM CDC.DBO_EMPLOYEES_CT order by 4
--STEP 6 Ins/Upd/Del
INSERT INTO Employees VALUES ('User7', 'Senior Software Engineer', 355000)
UPDATE Employees SET Name = 'User3Updated',Salary = 300000 WHERE ID = 3
DELETE FROM Employees WHERE ID = 4
--STEP 7
SELECT C.__$OPERATION AS OPERATION,
CASE
WHEN C.__$OPERATION =1 THEN 'DELETED'
WHEN C.__$OPERATION =2 THEN 'INSERTED'
WHEN C.__$OPERATION =3 THEN 'UPD-BEFORE CHANGE'
WHEN C.__$OPERATION =4 THEN 'UPD-AFTER CHANGE'
END AS DML_OPERATION,
C.ID, C.NAME,C.DESIGNATION,C.SALARY,
T.TRAN_BEGIN_TIME AS TRAN_BEGIN_TIME, TRAN_END_TIME AS TRAN_END_TIME
FROM CDC.DBO_EMPLOYEES_CT C
INNER JOIN [CDC].[LSN_TIME_MAPPING] T
ON C.__$START_LSN = T.START_LSN
Thank you
Support me by subscribing to my channel for more videos
Have a great day
-
Prakash Rajamani
Azure Senior Data Engineer
Bangalore
India
#CDC
#MicrosoftAzureforBeginners
#WhatIsAzure
#MicrosoftAzureTutorialforBeginners
#AzureTrainingforBeginners
#Azure
#MicrosoftAzure
#AzureforBeginners
#prakashrajamani
#praasytechnologies
#praasy
#prakashrajamani

[@akashghanghav5683]

Thanks Prakash

[@hd4558]

Thank you soo much for the brief and necessary information

[@manthrisrinivas6763]

can we get LastEditedBy and LastEdited Date though this CDC approach? Please give a example with description

[@chrisnol1750]

useful feature, thanks prakash

[@Praasy]

Thanks, is this Christopher Nolan?

[@chrisnol1750]

yes