How do I use SSMS to compare Database 1 Table 1 against Database 2 Table 1?

I have two databases - Database1 and Database2.

Both databases contain a table that has similar structure, exemplified as follows:

===========================================================================================
| ID | Name | PhoneNoFormat | DialingCountryCode | InternationalDialingCode | InternetTLD |
===========================================================================================
| | | | | | |
===========================================================================================

However, due to some reason, (a) one of the tables in one of the databases has data that is not exactly the same as (b) that contained in the other table in the another database.

So, how can I compare Database1.Table1 against Database2.Table1?

I tried using the following query, but nothing happened so was wondering if I have to rewrite it:

SELECT MIN(TableName) as TableName,
       ID,
       Name,
       PhoneNoFormat,
       DialingCountryCode,
       InternationalDialingCode,
       InternetTLD

FROM

(

  SELECT  'Table A' as TableName,
          A.ID,
          A.Name,
          A.PhoneNoFormat,
          A.DialingCountryCode,
          A.InternationalDialingCode,
          A.InternetTLD

  FROM [D:\DATABASE1.MDF].[dbo].[Table1] AS A

  UNION ALL

  SELECT 'Table B' as TableName,
         B.ID, B.Name,
         B.PhoneNoFormat,
         B.DialingCountryCode,
         B.InternationalDialingCode,
         B.InternetTLD

  FROM [D:\DATABASE2.MDF].[dbo].[Table1] AS B

) tmp

GROUP BY ID, Name, PhoneNoFormat, DialingCountryCode, InternationalDialingCode, InternetTLD

HAVING COUNT(*) = 1

ORDER BY ID

The name of your database can't be [D:\DATABASE1.MDF], that may be the path to the physical data file. So the complete table name (3 part name as in db.schema.table) is [DATABASE1].[dbo].[Table1].

If it's a one time task, use the Red Gate SQL Data Compare. It's a great tool and it offers a trial version.

If you have Visual Studio Professional you should also have Data Comparison Tool inside it. It's another great tool to compare data and schema.

If you don't have access to any other tools to compare the data in two tables, I would use tablediff.exe, which is included in the SQL Server installation. You'll find it in the SQL Server program path. Details here, on MSDN.

If you want to use SQL, than you can use the function binary_checksum (or its relative, checksum) to generate a hash and compare over two different versions of the same row, in case you can compare row by row. You can also use the functions for each column, not only for the complete row (off course, going row by row, based on a key). See an example here.

StackExchange Database Administrators Q#31593, answer score: 9