使用CDC查找更新的列列表和数据

Question

我正在创建一个设置，以维护使用CDC的历史数据表。下面是我写的脚本:

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

上面的脚本将返回数据已更改的列的值。我正在寻找下面的输出，即列FieldName将包含列列表，其中数据更新和值列将包含前一个和新的值。CreatedDate值将成为更新前行的开始日期，ModifiedDate将成为更新前行的结束日期和更新后行的开始日期。

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

我在我的表中检查了您的查询，我能够获得多次更新的相同行的结果。

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

如果你仍然没有得到，只需交叉检查你的跟踪表是否在每次更新时正确填充，并验证你为表启用cdc的参数。

Answer 2

我认为通过在表cdc上使用pivot函数。Name_CT，可以得到所需的输出结构