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 records from either table that do NOT match
-- the other table, along with which table the record 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)

DELETE D
FROM	DestDatabase.dbo.[MyTable] AS D
WHERE	NOT EXISTS
	(
	SELECT	*
	FROM	SourceDatabase.dbo.[MyTable] AS S
	WHERE
		    D.[MyPK1] = S.[MyPK1]
		AND D.[MyPK2] = S.[MyPK2]
	)
...
IF EXISTS (SELECT * FROM SourceDatabase.dbo.[MyTable])
BEGIN
UPDATE D
SET
	[MyCol1] = S.[MyCol1],
	[MyCol2] = S.[MyCol2],
...
FROM	DestDatabase.dbo.[MyTable] AS D
	JOIN SourceDatabase.dbo.[MyTable] AS S
		 ON D.[MyPK1] = S.[MyPK1]
		AND D.[MyPK2] = S.[MyPK2]
WHERE
		(
			   (D.[MyCol1] <> S.[MyCol1]
				   OR (D.[MyCol1] IS NULL AND S.[MyCol1] IS NOT NULL) 
				   OR (D.[MyCol1] IS NOT NULL AND S.[MyCol1] IS NULL))
			OR (D.[MyVarcharCol] COLLATE Latin1_General_BIN <> S.[MyVarcharCol]
				   OR (D.[MyVarcharCol] IS NULL AND S.[MyVarcharCol] IS NOT NULL)
				   OR (D.[MyVarcharCol] IS NOT NULL AND S.[MyVarcharCol] IS NULL))
...
		)

END
GO

IF EXISTS (SELECT * FROM SourceDatabase.dbo.[MyTable])
BEGIN
INSERT INTO DestDatabase.dbo.[MyTable]
(
	[MyCol1],
	[MyCol2],
...
)
SELECT	S.*
FROM	SourceDatabase.dbo.[MyTable] AS S
WHERE	NOT EXISTS
	(
	SELECT	*
	FROM	DestDatabase.dbo.[MyTable] AS D
	WHERE
		    D.[MyPK1] = S.[MyPK1]
		AND D.[MyPK2] = S.[MyPK2]
	)
END

quote:

---

Originally posted by jsmith8858

Based on the discussion at:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=23038

and BSmith's idea (no relation), I came up with this silly stored proc to compare any two tables or views. Supply the table names and the columns you wish to compare and have fun!

The key to the GROUP BY approach is that it handles NULLS quite well which JOINS have trouble with.

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 records from either table that do NOT match
-- the other table, along with which table the record 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)

- Jeff

---

DECLARE @t1 table (id int, t text);
DECLARE @t2 table (id int, t text);

insert @t1 values(1,replicate('abc',3000));
insert @t2 values(1,replicate('abc',3000));

select max(id) as ID, t
FROM
(
select id, CONVERT(varchar(max),t) as t from @t1
union all
select id, CONVERT(varchar(max),t) as t from @t2
) as z
GROUP BY t
HAVING COUNT(*) = 1