Compare the Data in Two Tables

/*
 These queries are used to compare the data in two tables.  
 In these examples all the rows in each table is retrieved and
 compared using the UNION ALL command.  I would recommend 
 adding additional WHERE criteria for very large tables to 
 reduce the number of rows that will be compared.

 These SQL statements are based on a stored procedure from this link
 http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=23054&SearchTerms=compare,table

*/
USE Adventureworks
GO

-- Compare the data in two tables for a subset of columns
--
SELECT Max(TableName) as TableName, databaselogid,posttime 
  FROM (
    SELECT 'DatabaseLog' AS TableName, databaselogid,posttime 
      FROM DatabaseLog 
     UNION ALL 
    SELECT 'DatabaseLog_test' As TableName, databaselogid,posttime 
      FROM DatabaseLog_test
  ) A 
GROUP BY databaselogid,posttime 
HAVING COUNT(*) = 1
ORDER BY databaselogid,posttime 

GO
-- Compare the data in two tables for all columns
-- except for XML data Types
--
DECLARE @SQL varchar(8000)
DECLARE @Table1 varchar(8000)
DECLARE @Table2 varchar(8000)
SET @Table1 = 'DatabaseLog'
SET @Table2 = 'DatabaseLog_test'

DECLARE @Cols varchar(8000)
SET @Cols = ''
SELECT @Cols = @Cols + '[' + Column_Name + '],'
  FROM INFORMATION_SCHEMA.Columns
 WHERE Table_Name = @Table1 and DATA_TYPE <> 'xml'
SET @Cols=left(@cols,LEN(@cols)-1) -- Remove trailing ,

SET @SQL = + 
'SELECT Max(TableName) as TableName, ' + @Cols +
'  FROM ( ' +
'    SELECT ''' + @Table1 + ''' AS TableName, ' + @Cols +
'      FROM ' + @Table1 +
'     UNION ALL ' +
'    SELECT ''' + @Table2 + ''' As TableName, ' + @Cols +
'      FROM ' + @Table2 +
'    ) A ' +
' GROUP BY ' + @Cols +
' HAVING COUNT(*) = 1 ' +
' ORDER BY ' + @Cols

-- SELECT @SQL
EXEC ( @SQL)