SQL : Table Two-Way Column Comparison

SQL : Table Two-Way Column Comparison

This article was originally posted here: http://weblogs.sqlteam.com/jeffs/archive/2004/11/10/2737.aspx I have found it useful so thought I would repost it here.

The following SQL will create a stored procedure that will perform a two-way comparison of columns in different tables.

CREATE PROCEDURE CompareTables(@table1 varchar(100),
[email protected] Varchar(100), @T1ColumnList varchar(1000),
[email protected] varchar(1000) = ”)
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, ‘ +
[email protected] + ‘ FROM ‘ + @Table2
set @SQL = ‘SELECT Max(TableName) as TableName, ‘ + @t1ColumnList +
 ‘ FROM (‘ + @SQL + ‘) A GROUP BY ‘ + @t1ColumnList +
 ‘ HAVING COUNT(*) = 1’
exec ( @SQL)

You can execute the query using the command:

DECLARE    @return_value int
EXEC    @return_value = [dbo].[CompareTables]
        @table1 = N’otProductSiteInclusion’,
        @table2 = N’otDealers’,
        @T1ColumnList = N’dealerid’,
        @T2ColumnList = N’dealerid’
SELECT    ‘Return Value’ = @return_value
GO