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),
@table2 Varchar(100), @T1ColumnList varchar(1000),
@T2ColumnList 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, ‘ +
@t2ColumnList + ‘ 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