UNION allows you to compare all columns very quickly, and also handles comparing NULL values to other NULLs successfully, which a join clause or a WHERE condition doesn't normally do. It also allows you to very quickly see which rows are missing in either table, which only a FULL OUTER JOIN will do, but of course we all know to avoid those at all costs (right?) -- a full outer join is about as “unrelational” as you can get. (every column returned is potentially Null and must be wrapped in a COALESCE function). Best of all, the UNION is quick and easy and short.
The basic idea is: if we GROUP the union of two tables on all columns, then if the two tables are identical all groups will result in a COUNT(*) of 2. But for any rows that are not completely matched on any column in the GROUP BY clause, the COUNT(*) will be 1 -- and those are the ones we want. We also need to add a column to each part of the UNION to indicate which table each row comes from, otherwise there is no way to distinguish between which row comes from which table.
declare @table1 varchar(100), @table2 Varchar(100), @ColumnList varchar(1000), @ColumnKey varchar(100)set @table1='[BMO_AVTLFeeder]'set @table2='[BMO_AVTLFeeder_Backup]'set @ColumnList='FdrID, FdrNameTemplate, FdrExpectedTime'set @ColumnKey='FdrID'declare @SQL varchar(8000);set @SQL = 'SELECT MIN(TableName) as TableName, ' + @ColumnList
+ ' FROM ('+ ' SELECT '''+ @table1 +''' as TableName, ' + @ColumnList
+ ' FROM '+@table1+' A'+ ' UNION ALL'+ ' SELECT ''' + @table2 + ''' as TableName, ' + @ColumnList
+ ' FROM '+@table2+' B'+ ' ) tmp'+ ' GROUP BY ' + @ColumnList
+ ' HAVING COUNT(*) = 1'+ ' ORDER BY '+ @ColumnKey
print @SQL
exec (@SQL)
SELECT 'Table1' AS TableName, name, lastname FROM
Table1 OUTER JOIN Table2 ON Table1.name = Table2.name2
AND Table1.lastname = Table2.lastnameWHERE Table2.name2 IS NULL
UNION
SELECT 'Table2' AS TableName, name2 as name, lastname2 as lastname FROM
Table2 OUTER JOIN Table1 ON Table2.name2 = Table1.name
AND Table2.lastname2 = Table1.lastnameWHERE Table1.name IS NULL
