SqlDataComparison_Gist5.sql

SET @SQL = 'INSERT INTO ' + @local_table_name + ' (' + @CRLF

SELECT @SQL = @SQL + @TAB + '[' + #columns.name + '],' + @CRLF
FROM #columns

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

SELECT @SQL = @SQL + ')' + @CRLF

SELECT @SQL = @SQL + 'SELECT' + @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 '  AND ' END +
		'[ours].[' + #key_columns.name + '] IS NULL' + @CRLF,
	@i = @i + 1
FROM #key_columns