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

Question

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

Answer 1

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.

Answer 2

--Rows in DB1 which are absent from DB2
select * from DATABASE1.dbo.Table1
except
select * from DATABASE2.dbo.Table1

--Rows in DB2 which are absent from DB1
select * from DATABASE2.dbo.Table1
except
select * from DATABASE1.dbo.Table1

Answer 3

You can use EXCEPT as a quick and dirty way to compare data in two tables with identical column structure.

SELECT *
FROM Database1.dbo.Table1
EXCEPT
SELECT *
FROM Database2.dbo.Table1

This will find rows in Database1.dbo.Table1 that are not present in Database2.dbo.Table1 (i.e. rows in Database1.dbo.Table1 that don't have an exact column-for-column match in Database2.dbo.Table1).

If you know the rows should have the same primary key values, you can join on that key and view mismatched rows side by side:

SELECT *
FROM Database1.dbo.Table1 a
    INNER JOIN Database2.dbo.Table1 b
        ON a.id = b.id
WHERE EXISTS (SELECT a.* EXCEPT SELECT b.*)

That one is an old trick I found somewhere on this site.

I wouldn't use this in any production code, what with all the SELECT *, but for quick interactive querying, there shouldn't be any harm.

Answer 4

If you are looking for something more visual, one trick is to use any compassion tool (in my case Devart Code Compare) and copy/paste the content (select *) of every table: