SQL Server 2008同库两表全列数据差异对比方案问询

手动替换70个列名确实太折磨人了！针对SQL Server 2008的场景，我们可以用动态SQL自动遍历所有列来完成对比，下面给你两种实用的方案，按需选择：

方案1：返回详细差异明细（FormID+列名+新旧值） #

这个方案会把每个列的差异单独成行展示，清晰看到具体哪个列、哪个FormID存在差异，以及新旧值是什么：

DECLARE @Columns NVARCHAR(MAX)
DECLARE @SQL NVARCHAR(MAX)

-- 获取所有需要对比的列（排除关联键FormID）
SELECT @Columns = STUFF((
    SELECT ', ' + QUOTENAME(c.name)
    FROM sys.columns c
    WHERE c.object_id = OBJECT_ID('NEEF_Entry.dbo.tbl_TOF')
      AND c.name <> 'FormID'
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 2, '')

-- 构建动态SQL，通过UNPIVOT将列转为行后对比差异
SET @SQL = N'
WITH TOF_Unpivoted AS (
    SELECT FormID, ColumnName, ColumnValue
    FROM NEEF_Entry.dbo.tbl_TOF
    UNPIVOT (
        ColumnValue FOR ColumnName IN (' + @Columns + ')
    ) AS up
),
TOF_Old_Unpivoted AS (
    SELECT FormID, ColumnName, ColumnValue
    FROM NEEF_Entry.dbo.tbl_TOF_old
    UNPIVOT (
        ColumnValue FOR ColumnName IN (' + @Columns + ')
    ) AS up
)
SELECT 
    t.FormID,
    t.ColumnName,
    OldValue = o.ColumnValue,
    NewValue = t.ColumnValue
FROM TOF_Unpivoted t
JOIN TOF_Old_Unpivoted o 
    ON t.FormID = o.FormID 
    AND t.ColumnName = o.ColumnName
WHERE 
    t.ColumnValue <> o.ColumnValue
    -- 处理NULL值差异：NULL和非NULL的情况不会被<>捕获
    OR (t.ColumnValue IS NULL AND o.ColumnValue IS NOT NULL)
    OR (t.ColumnValue IS NOT NULL AND o.ColumnValue IS NULL)'

-- 执行动态SQL
EXEC sp_executesql @SQL

方案说明： #

• 用UNPIVOT把每个列的内容转成一行数据，这样就能统一对比所有列
• 专门处理了NULL值的差异，避免漏掉这类特殊情况
• 结果集中每一行对应一个具体的列差异，排查问题更高效

方案2：返回整行对比结果（带差异标记） #

如果需要看到某条FormID对应的所有列的新旧值，同时标记哪些列有差异，可以用这个方案：

DECLARE @ColumnsSelect NVARCHAR(MAX)
DECLARE @ColumnsWhere NVARCHAR(MAX)
DECLARE @SQL NVARCHAR(MAX)

-- 生成SELECT部分：包含每个列的新旧值+差异状态标记
SELECT @ColumnsSelect = STUFF((
    SELECT N', 
    t.' + QUOTENAME(c.name) + ' AS New_' + c.name + ',
    o.' + QUOTENAME(c.name) + ' AS Old_' + c.name + ',
    CASE WHEN t.' + QUOTENAME(c.name) + ' <> o.' + QUOTENAME(c.name) 
        OR (t.' + QUOTENAME(c.name) + ' IS NULL AND o.' + QUOTENAME(c.name) + ' IS NOT NULL)
        OR (t.' + QUOTENAME(c.name) + ' IS NOT NULL AND o.' + QUOTENAME(c.name) + ' IS NULL)
        THEN ''DIFFERENT'' ELSE ''SAME'' END AS ' + QUOTENAME(c.name + '_Status')
    FROM sys.columns c
    WHERE c.object_id = OBJECT_ID('NEEF_Entry.dbo.tbl_TOF')
      AND c.name <> 'FormID'
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 2, '')

-- 生成WHERE部分：只要有任意一列存在差异就返回该行
SELECT @ColumnsWhere = STUFF((
    SELECT N' OR (t.' + QUOTENAME(c.name) + ' <> o.' + QUOTENAME(c.name) 
    + ' OR (t.' + QUOTENAME(c.name) + ' IS NULL AND o.' + QUOTENAME(c.name) + ' IS NOT NULL)'
    + ' OR (t.' + QUOTENAME(c.name) + ' IS NOT NULL AND o.' + QUOTENAME(c.name) + ' IS NULL))'
    FROM sys.columns c
    WHERE c.object_id = OBJECT_ID('NEEF_Entry.dbo.tbl_TOF')
      AND c.name <> 'FormID'
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 4, '')

-- 构建并执行动态SQL
SET @SQL = N'
SELECT 
    t.FormID,
    ' + @ColumnsSelect + '
FROM NEEF_Entry.dbo.tbl_TOF t
JOIN NEEF_Entry.dbo.tbl_TOF_old o ON t.FormID = o.FormID
WHERE ' + @ColumnsWhere

EXEC sp_executesql @SQL

方案说明： #

• 每个列会生成三个字段：新值、旧值、差异状态（DIFFERENT或SAME）
• 只会返回至少有一个列存在差异的行，减少无效数据
• 适合需要整体查看某条记录所有列变化的场景

注意事项： #

• 确保两个表的列名完全一致（可以通过SELECT name FROM sys.columns WHERE object_id = OBJECT_ID('表名')验证）
• 因为SQL Server 2008没有STRING_AGG函数，我们用了FOR XML PATH来拼接列名，这种方式兼容所有列名（包括含特殊字符的列）
• 一定要处理NULL值差异，否则会漏掉NULL vs 非NULL这类情况

内容的提问来源于stack exchange，提问作者ZAIN-UL ABDIN