user1921849 user1921849 - 6 months ago 27
SQL Question

SQL Server, compare 2 tables with dynamic columns

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.

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


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

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.

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

Does anyone have code like this?

Answer

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

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)

Output:

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