sql-server - SQL Server 中如何比较两个结构相似的表，只返回列名和不同值的值？

Question

我使用 SQL Server 2008 (v10.0 SP3) 作为我的数据库。

我试图找到如何明智地比较两个几乎相同的表结构，并且只返回不匹配的列的列名和值。

我有两张 table 。

表 A 大约有 260 列，每条记录都有一个唯一标识符。它来自另一台服务器上的 View 。

表 B 是表 A 的结构副本，添加了插入日期列和操作列。

IF OBJECT_ID('tempdb..#TableA') IS NOT NULL
    DROP TABLE #TableA
IF OBJECT_ID('tempdb..#TableB') IS NOT NULL
    DROP TABLE #TableB

CREATE TABLE #TableA (
UniqueID INT,[Name] CHAR(3),[Address] CHAR(15),
HairColor CHAR(6),ImportDate DATETIME
)

CREATE TABLE #TableB (
UniqueID INT,[Name] CHAR(3),[Address] CHAR(15),
HairColor CHAR(6),ImportDate DATETIME,
AuditDate DATETIME,[Action] CHAR(10)
)

INSERT INTO #TableA
VALUES (1,'Joe','1 Main St.','Brown','12/1/2013')

INSERT INTO #TableA
VALUES (2,'Jen','1 Main St.','Red','12/1/2013')

INSERT INTO #TableB
VALUES (2,'Jen','1 Main St.','Blonde','10/1/2013','12/1/2013','CHANGE')

INSERT INTO #TableB
VALUES (2,'Jen','1 Baker St.','Blonde','4/1/2010','10/1/2013','CHANGE')

INSERT INTO #TableB
VALUES (2,'Jen','4 Deer Ave.','Black','6/1/2004','4/1/2010','CHANGE')

SELECT * FROM #TableA AS ta

SELECT * FROM #TableB AS tb

表A

╔══════════╦══════╦════════════╦═══════════╦════════════╗
║ UniqueID ║ Name ║  Address   ║ HairColor ║ ImportDate ║
╠══════════╬══════╬════════════╬═══════════╬════════════╣
║        1 ║ Joe  ║ 1 Main St. ║ Brown     ║ 12/1/2013  ║
║        2 ║ Jen  ║ 1 Main St. ║ Red       ║ 12/1/2013  ║
╚══════════╩══════╩════════════╩═══════════╩════════════╝

表B

╔══════════╦══════╦═════════════╦═══════════╦════════════╦═══════════╦════════╗
║ UniqueID ║ Name ║   Address   ║ HairColor ║ ImportDate ║ AuditDate ║ Action ║
╠══════════╬══════╬═════════════╬═══════════╬════════════╬═══════════╬════════╣
║        2 ║ Jen  ║ 1 Main St.  ║ Blonde    ║ 10/1/2013  ║ 12/1/2013 ║ CHANGE ║
║        2 ║ Jen  ║ 1 Baker St. ║ Blonde    ║ 4/1/2010   ║ 10/1/2013 ║ CHANGE ║
║        2 ║ Jen  ║ 4 Deer Ave. ║ Black     ║ 6/1/2004   ║ 4/1/2010  ║ CHANGE ║
╚══════════╩══════╩═════════════╩═══════════╩════════════╩═══════════╩════════╝

表 A 每个月都会被截断并重新加载，但在此之前，会比较表 A 中的新数据(新 A)和表 A 中已有的数据(旧 A)。

如果新 A 不包含旧 A 中的行，我使用 insertdate 和 DELETED 将旧 A 行插入到 Table B作为 Action 。

如果新 A 包含旧 A 中不存在的行，我将新 A 行插入到 Table B 中，并使用 insertdate 和 ADDED 作为行动。

如果新 A 包含的行中有任何列与旧 A 不匹配，我将旧 A 行插入 Table B 并使用 insertdate 和 CHANGE 作为 Action 。

如您所见，表 B 可以有多行与表 A 具有相同的唯一标识符，因为表 A 中唯一记录的源数据可能会逐月变化。

现在增删改查很容易上报。我遇到的困难是报告更改。对于 260 个公共(public)行，我希望能够选择 仅 表 B 中与表 A 中的相关列不匹配的列，左连接 TableA.UniqueID = TableB.UniqueID

我正在考虑使用表 A 中的输入 UniqueID 制作一个表值函数并输出如下内容:

╔══════════╦════════════╦═════════════╦════════════╦═══════════╦════════╗
║ UniqueID ║ ColumnName ║    Value    ║ ImportDate ║ AuditDate ║ Action ║
╠══════════╬════════════╬═════════════╬════════════╬═══════════╬════════╣
║        2 ║ HairColor  ║ Blonde      ║ 10/1/2013  ║ 12/1/2013 ║ CHANGE ║
║        2 ║ Address    ║ 1 Baker St. ║ 4/1/2010   ║ 10/1/2013 ║ CHANGE ║
║        2 ║ HairColor  ║ Blonde      ║ 4/1/2010   ║ 10/1/2013 ║ CHANGE ║
║        2 ║ Address    ║ 4 Deer Ave. ║ 6/1/2004   ║ 4/1/2010  ║ CHANGE ║
║        2 ║ HairColor  ║ Black       ║ 6/1/2004   ║ 4/1/2010  ║ CHANGE ║
╚══════════╩════════════╩═════════════╩════════════╩═══════════╩════════╝

我只是不知道该怎么做。

这是我的大脑停止工作的地方。是否可以在 INFORMATION_SCHEMA.COLUMNS 中使用 ORDINAL_POSITION 对我有利？

SELECT a.ORDINAL_POSITION,a.COLUMN_NAME 
FROM INFORMATION_SCHEMA.COLUMNS AS a 
JOIN INFORMATION_SCHEMA.COLUMNS AS b ON a.COLUMN_NAME = b.COLUMN_NAME
WHERE a.TABLE_NAME = 'TableA' AND a.TABLE_SCHEMA='dbo'
AND b.TABLE_NAME = 'TableB' AND b.TABLE_SCHEMA='dbo'

Answer 1

听起来 UNPIVOT 就是您所需要的。来自 MSDN:Using PIVOT and UNPIVOT

以下是您问题的可能解决方案:

SELECT UniqueID, ColumnName, Value, ImportDate, AuditDate, Action
FROM
(
    SELECT 
        TableA.UniqueID, 
        CAST((CASE WHEN ISNULL(TableB.Address, '') <> ISNULL(TableA.Address, '')
              THEN ISNULL(TableB.Address, '')
              ELSE NULL END) AS nvarchar(255))
        AS Address, 
        CAST((CASE WHEN ISNULL(TableB.HairColor, '') <> ISNULL(TableA.HairColor, '')
              THEN ISNULL(TableB.HairColor, '')
              ELSE NULL END) AS nvarchar(255))
        AS HairColor, 
        TableB.ImportDate,
        TableB.AuditDate,
        TableB.Action
    FROM TableA INNER JOIN TableB ON TableB.UniqueID = TableA.UniqueID
) AS p
UNPIVOT
(
    Value FOR ColumnName in (Address, HairColor)
) AS up
WHERE Value IS NOT NULL

请注意，我将值转换为 nvarchar(255) 以解决类型之间的冲突。您可能需要将其调整为更合适的内容。

此外，乍一看，我确实认为生成的结果可能需要根据导入和审核日期进行一些改进。但那是另一个话题。另外，我最终使用了 INNER JOIN，因为在这种情况下 LEFT JOIN 对我来说没有意义。也许我遗漏了什么。

综上所述，这应该会得到您正在寻找的结果。