SQL Server，将 2 个表与动态列进行比较

2022-01-14 00:00:00 automated-tests sql sql-server

我正在寻找可以比较具有相同架构的 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

查询(或存储过程)的输出将是:

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.

有人有这样的代码吗?

推荐答案

快速而肮脏的答案:这会逐列创建并比较两个表(必须与您指示的架构相同)值.

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

相关文章