To find updated column list and data using CDC

Question

I am creating a setup to maintain a table with historical data using CDC. Below is the script I have written:

create table dbo.Name (ID int not null primary key clustered identity(1,1),
Col1 nvarchar(50) not null constraint DF_Col1 default 'Unknown',
Col2 nvarchar(50) not null constraint DF_Col2 default 'Unknown',
Col3 nvarchar(50) not null constraint DF_Col3 default 'Unknown',
Col4 nvarchar(50) not null constraint DF_Col4 default 'Unknown',
Col5 nvarchar(50) not null constraint DF_Col5 default 'Unknown',
CreatedDate DATETIME NOT NULL DEFAULT(GETDATE()),
ModifiedDate DATETIME 
)
GO

exec sys.sp_cdc_enable_db
go
exec sys.sp_cdc_enable_table @source_schema = N'dbo',
@source_name = N'Name',
@capture_instance = 'Name', 
@supports_net_changes = 1, 
@role_name = NULL
GO

INSERT INTO dbo.Name 
VALUES('A','B','C','D','E',GETDATE(),NULL),
('F','G','H','I','J',GETDATE(),NULL),
('K','L','M','N','O',GETDATE(),NULL)
GO

SELECT * FROM cdc.Name_CT

UPDATE Name
SET Col1 = Col1 + '_U', ModifiedDate = GETDATE()
WHERE id = 1

UPDATE Name
SET Col2 = Col2 + '_V', ModifiedDate = GETDATE()
WHERE id = 2 

UPDATE Name
SET Col3 = Col3 + '_A', Col4 = Col4 + '_B', ModifiedDate = GETDATE()
WHERE id = 3
GO

SELECT * FROM cdc.Name_CT

The above script will return the values of the columns where the data has changed. I am looking for the below output i.e. The column FieldName will contain the column list where the data is updated and value column will contain the previous and the new value. CreatedDate value will become the StartDate of the row before updation and ModifiedDate will become the EndDate of before update row and StartDate of after update row.

ID    FieldName    Value    StartDate                 EndDate
=================================================================
1     Col1         A        2014-08-18 15:56:08       2014-08-18 15:59:44
1     Col1         A_U      2014-08-18 15:59:44       NULL
2     Col2         G        2014-08-18 15:56:08       2014-08-18 15:59:44
2     Col2         G_V      2014-08-18 15:59:44       NULL
3     Col3         M        2014-08-18 15:56:08       2014-08-18 15:59:44
3     Col3         M_A      2014-08-18 15:59:44       NULL
3     Col4         N        2014-08-18 15:56:08       2014-08-18 15:59:44
3     Col4         N_B      2014-08-18 15:59:44       NULL

Answer 1

I checked with your query in my table and i am able to get the result for same rows updated multiple times.

With Cte_CDC as (SELECT ID,FieldName,FieldValue,StartDate=CreatedDate,EndDate 

=ModifiedDate
from 
(
Select ID,Col1,Col2,Col3,Col4,Col5,CreatedDate,ModifiedDate 
FROM cdc.mssqlserver2012_ct
) a
Unpivot
(
FieldValue for FieldName in (Col1,Col2,Col3,Col4,Col5)
) Upt)


select distinct a.ID,a.FieldName,a.FieldValue,ISNULL(a.EndDate,a.StartDate) as Startdate,b.EndDate
from Cte_CDC a
join Cte_CDC b on a.ID=b.ID and a.FieldName=b.FieldName and a.FieldValue<>b.FieldValue
order by 1,2

ID  FieldName   FieldValue  Startdate   EndDate
1   Col1    A   2014-08-20 15:09:40.560 2014-08-20 15:11:34.863
1   Col1    A   2014-08-20 15:09:40.560 2014-08-20 15:12:46.117
1   Col1    A   2014-08-20 15:09:40.560 2014-08-20 15:18:15.973
1   Col1    A_U 2014-08-20 15:11:34.863 NULL
1   Col1    A_U 2014-08-20 15:11:34.863 2014-08-20 15:12:46.117
1   Col1    A_U 2014-08-20 15:11:34.863 2014-08-20 15:18:15.973
1   Col1    A_U_UV  2014-08-20 15:12:46.117 NULL
1   Col1    A_U_UV  2014-08-20 15:12:46.117 2014-08-20 15:11:34.863
1   Col1    A_U_UV  2014-08-20 15:12:46.117 2014-08-20 15:18:15.973
1   Col1    A_U_UV_UVX  2014-08-20 15:18:15.973 NULL
1   Col1    A_U_UV_UVX  2014-08-20 15:18:15.973 2014-08-20 15:11:34.863
1   Col1    A_U_UV_UVX  2014-08-20 15:18:15.973 2014-08-20 15:12:46.117
2   Col2    G   2014-08-20 15:09:40.560 2014-08-20 15:11:34.877
2   Col2    G_V 2014-08-20 15:11:34.877 NULL
3   Col3    M   2014-08-20 15:09:40.560 2014-08-20 15:11:34.877
3   Col3    M_A 2014-08-20 15:11:34.877 NULL
3   Col4    N   2014-08-20 15:09:40.560 2014-08-20 15:11:34.877
3   Col4    N_B 2014-08-20 15:11:34.877 NULL

If your still not getting, just cross check your tracking table weather it is populating properly or not for each update and verify your arguments for enabling the cdc for your table.

Answer 2

I think by using pivot function on the table cdc.Name_CT, you can get your desired output structure.