我有很多大表,每个表都有> 300列.我正在使用的应用程序通过在辅助表中创建当前行的副本来创建已更改行的“存档”.
考虑一个简单的例子：
CREATE TABLE dbo.bigtable ( UpdateDate datetime,PK varchar(12) PRIMARY KEY,col1 varchar(100),col2 int,col3 varchar(20),. . . colN datetime );
档案表：
CREATE TABLE dbo.bigtable_archive ( UpdateDate datetime,PK varchar(12) NOT NULL,. . . colN datetime );
在dbo.bigtable上执行任何更新之前,将在dbo.bigtable_archive中创建该行的副本,然后使用当前日期更新dbo.bigtable.UpdateDate.
因此将两张桌子联合起来在按UpdateDate排序时,按PK分组会创建更改的时间轴.
我希望创建一个报告,详细说明由UpdateDate排序的行之间的差异,按PK分组,格式如下：
PK,UpdateDate,ColumnName,Old Value,New Value
旧值和新值可以是转换为VARCHAR(MAX)的相关列(不涉及TEXT或BYTE列),因为我不需要对值本身进行任何后处理.
目前我无法想到一个理智的方法来为大量的列执行此操作,而无需以编程方式生成查询 – 我可能必须这样做.
开放给很多想法,所以我会在2天后为问题添加赏金.
解决方法
这看起来并不漂亮,特别是考虑到超过300列和LAG不可用,也不是表现得非常好,但就像开始时一样,我会尝试以下方法：
> UNION这两张桌子.
>对于组合集中的每个PK,从归档表中获取其先前的“化身”(下面的实现使用OUTER APPLY TOP(1)作为穷人的LAG).
>将每个数据列转换为varchar(max)并成对地取消它们,即当前值和之前的值(CROSS APPLY(VALUES …)适用于此操作).
>最后,根据每对中的值是否彼此不同来过滤结果.
我看到的上面的Transact-sql：
WITH
Combined AS
(
SELECT * FROM dbo.bigtable
UNION ALL
SELECT * FROM dbo.bigtable_archive
) AS derived,OldAndNew AS
(
SELECT
this.*,OldCol1 = last.Col1,OldCol2 = last.Col2,...
FROM
Combined AS this
OUTER APPLY
(
SELECT TOP (1)
*
FROM
dbo.bigtable_archive
WHERE
PK = this.PK
AND UpdateDate < this.UpdateDate
ORDER BY
UpdateDate DESC
) AS last
)
SELECT
t.PK,t.UpdateDate,x.ColumnName,x.OldValue,x.NewValue
FROM
OldAndNew AS t
CROSS APPLY
(
VALUES
('Col1',CAST(t.OldCol1 AS varchar(max),CAST(t.Col1 AS varchar(max))),('Col2',CAST(t.OldCol2 AS varchar(max),CAST(t.Col2 AS varchar(max))),...
) AS x (ColumnName,OldValue,NewValue)
WHERE
NOT EXISTS (SELECT x.OldValue INTERSECT x.NewValue)
ORDER BY
t.PK,x.ColumnName
;
