SQL Server，将2个表与动态列进行比较 [英] SQL Server, compare 2 tables with dynamic columns

问题描述

我正在寻找将2个表与相同模式进行比较的代码，并告诉我哪些列有区别.

I am looking for code that will compare 2 tables with the same schema and tell me what columns have a difference.

我确定我可以编写这段代码，但是希望节省时间，精力和测试.

I'm sure I could write this code, but was hoping to save the time and effort and testing.

基本上，我有2个表，这些表一直在变化，就像这样:

Basically, I have 2 tables that change all the time that look like this:

KeyField  Cola Colb Colc Cold

查询(或存储的proc)的输出为:

Output from query (or stored proc) would be:

Keyfield, Column name,  Table 1 value, Table 2 value

可能有100个字段，所以我不想继续手动操作.

There may be 100 fields or so I don't want to keep doing this manually.

我可以做一个EXCEPT来查找哪些行不同(我正在测试数据仓库).但是，然后我必须手动查看哪些列不同.列根据测试而变化，我希望我可以重用某些内容.

I can do an EXCEPT to find which rows are different (I am doing testing for a data warehouse). However, then I have to manually go look which column(s) are different. The columns change depending on the test and I would like something I can reuse.

我认为这将需要动态sql以及显然需要系统表才能获取列名.

I think this will require dynamic sql and obviously the system tables to get the column names.

有人有这样的代码吗?

Does anyone have code like this?

推荐答案

快速而又肮脏的答案:这将逐列创建和比较两个表(必须具有与您指示的模式相同)的值.

Quick and dirty answer: this creates and compares two tables (must have same schema as you indicate) values on a column by column basis.

它仅显示两个不相等的值，而不是所有列.这不包含空处理或错误处理.同样不建议在可能受到SQL注入的情况下使用此方法.一旦测试了它，就取消对EXEC的注释以运行动态SQL.

It only shows the two values which are unequal, not all columns. This contains no null handling or error handling. It's also not advisable to use this where it could be subject to a SQL injection. Uncomment the EXEC to run the dynamic SQL once you test it.

USE TEMPDB
GO
DECLARE @SQL NVARCHAR(MAX), @SQL_OR NVARCHAR(MAX), @SQL_CASE NVARCHAR(MAX)
SET @SQL=''
SET @SQL_OR=''
SET @SQL_CASE=''
IF OBJECT_ID('tempdb.dbo.tmp1') IS NOT NULL DROP TABLE tempdb.dbo.tmp1
IF OBJECT_ID('tempdb.dbo.tmp2') IS NOT NULL DROP TABLE tempdb.dbo.tmp2

CREATE TABLE tempdb.dbo.tmp1 (keyField int identity(1,1), value1 int, value2 int)
CREATE TABLE tempdb.dbo.tmp2 (keyField int identity(1,1), value1 int, value2 int)

INSERT INTO tempdb.dbo.tmp1 (value1, value2)
VALUES (555,1204),
       (999,1255),
       (666,9999),
       (12345,12345)
INSERT INTO tempdb.dbo.tmp2 (value1, value2)
VALUES (555,1205),
       (888,1255), 
       (666,9999),
       (12345,NULL)

SELECT @SQL_OR=@SQL_OR+' OR ISNULL(T1.['+TBL1.COLUMN_NAME+'],'''')!=ISNULL(T2.['+TBL1.COLUMN_NAME+'],'''')' + CHAR(13),
       @SQL_CASE=@SQL_CASE+', CASE WHEN ISNULL(T1.['+TBL1.COLUMN_NAME+'],'''')!=ISNULL(T2.['+TBL1.COLUMN_NAME+'],'''') THEN ISNULL(CONVERT(NVARCHAR,T1.['+TBL1.COLUMN_NAME+']),''NULL'')+'' != ''+ISNULL(CONVERT(NVARCHAR,T2.['+TBL1.COLUMN_NAME+']),''NULL'') ELSE NULL END AS ['+TBL1.COLUMN_NAME+']' + CHAR(13)
FROM INFORMATION_SCHEMA.COLUMNS TBL1
WHERE TBL1.TABLE_NAME='tmp1'
AND TBL1.COLUMN_NAME!='keyField'
AND EXISTS (SELECT 1 
            FROM INFORMATION_SCHEMA.COLUMNS TBL2 
            WHERE TBL2.TABLE_NAME='tmp2' 
            AND TBL2.COLUMN_NAME!='keyField' 
            AND TBL1.COLUMN_NAME=TBL2.COLUMN_NAME)


SET @SQL = 'SELECT T1.keyField'+@SQL_CASE+' 
            FROM tempdb.dbo.tmp1 T1
            LEFT JOIN tempdb.dbo.tmp2 T2
               ON T1.keyField=T2.keyField
            WHERE 1=2' + @SQL_OR

PRINT @SQL
--EXEC(@SQL)

输出:

keyField    value1      value2
1           NULL        1204 != 1205
2           999 != 888  NULL
4           NULL        12345 != NULL

这篇关于SQL Server，将2个表与动态列进行比较的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！