SqlDataComparison_Gist3.sql

DECLARE @SQL NVARCHAR(MAX)
DECLARE @i INT

SET @SQL = 'SELECT '

SET @SQL = @SQL + '''OURS <<<'' AS [ ],' + @CRLF
SELECT @SQL = @SQL + @TAB + '   [ours].[' + #columns.name + '],' + @CRLF
FROM #columns

SET @SQL = @SQL + @TAB + '   ''THEIRS >>>'' AS [ ],' + @CRLF
SELECT @SQL = @SQL + @TAB + '   [theirs].[' + #columns.name + '],' + @CRLF
FROM #columns

SELECT @SQL = SUBSTRING(@SQL, 1, LEN(@SQL) - LEN(@CRLF) - 1) + @CRLF

SELECT @SQL = @SQL + 'FROM ' + @local_table_name + ' [ours]' + @CRLF
SELECT @SQL = @SQL + 'FULL OUTER JOIN ' + @remote_table_name + ' [theirs]' + @CRLF

SET @i = 0
SELECT
	@SQL = @SQL + CASE WHEN @i = 0 THEN 'ON' ELSE 'AND' END + ' [ours].[' + #key_columns.name + '] = [theirs].[' + #key_columns.name + ']' + @CRLF,
	@i = @i + 1
FROM #key_columns

SET @i = 0
SELECT
	@SQL = @SQL +
		CASE WHEN @i = 0 THEN 'WHERE ' ELSE '   OR ' END +
		'[ours].[' + #key_columns.name + '] IS NULL AND [theirs].[' + #key_columns.name + '] IS NOT NULL' + @CRLF +
		'   OR [ours].[' + #key_columns.name + '] IS NOT NULL AND [theirs].[' + #key_columns.name + '] IS NULL' + @CRLF,
	@i = @i + 1
FROM #key_columns

SELECT
	@SQL = @SQL +
		'   OR [ours].[' + #columns.name + '] IS NULL AND [theirs].[' + #columns.name + '] IS NOT NULL' + @CRLF +
		'   OR [ours].[' + #columns.name + '] IS NOT NULL AND [theirs].[' + #columns.name + '] IS NULL' + @CRLF +
		'   OR [ours].[' + #columns.name + '] <> [theirs].[' + #columns.name + ']' + @CRLF,
	@i = @i + 1
FROM #columns
LEFT OUTER JOIN #key_columns
ON #columns.column_id = #key_columns.column_id
WHERE #key_columns.column_id IS NULL