Almost a year ago I found a script for a stored procedure that allowed you to compare two tables in SQL. Today I’ve been working on some data verification, and made really good use out of this script. I couldn’t find it on my blog, so I don’t think I’ve posted this before. Unfortunately, I did not save the original author of the script or where I got it from.
One thing that I wish it could do was work on Table variables, but beggars can’t be choosers. Instead, I just create #TempTables and pass them into the stored procedure, and it works just fine. This lets you define either one or both of the tables you need to operate on, and write complex queries to populate the temp tables.
ALTER PROCEDURE [dbo].[tsi_sp_util_CompareTables](@table1 varchar(100),
@table2 Varchar(100), @T1ColumnList varchar(max),
@T2ColumnList varchar(max) = ”)
AS
– Table1, Table2 are the tables or views to compare.
– T1ColumnList is the list of columns to compare, from table1.
– Just list them comma-separated, like in a GROUP BY clause.
– If T2ColumnList is not specified, it is assumed to be the same
– as T1ColumnList. Otherwise, list the columns of Table2 in
– the same order as the columns in table1 that you wish to compare.
–
– The result is all rows from either table that do NOT match
– the other table in all columns specified, along with which table that
– row is from.
declare @SQL varchar(8000);
IF @t2ColumnList = ” SET @T2ColumnList = @T1ColumnList
set @SQL = ‘SELECT ”’ + @table1 + ”’ AS TableName, ‘ + @t1ColumnList +
‘ FROM ‘ + @Table1 + ‘ UNION ALL SELECT ”’ + @table2 + ”’ As TableName, ‘ +
@t2ColumnList + ‘ FROM ‘ + @Table2
set @SQL = ‘SELECT Max(TableName) as TableName, ‘ + @t1ColumnList +
‘ FROM (‘ + @SQL + ‘) A GROUP BY ‘ + @t1ColumnList +
‘ HAVING COUNT(*) = 1′
exec ( @SQL)