compare two databases

[nathanharcup]

Hi All,
I'm trying to compare data in two databases for all tables, I found the below code which works OK but does not work for text, ntext columns

Below is the code im using

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'

Is there a way of using UNION ALL with text, ntext ?

Thanks
Nathan

 

[markros]

If you're using SQL Server 2005 and up try casing text to varchar(max) and ntext to nvarchar(max).

In SQL Server 2000 you may try using substring(text,1,8000) and substring(ntext,1,4000) - so it will be good for that number of characters.

PluralSight Learning Library