Comparing Two Tables in SQL
Friday, July 31, 2009
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)