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

Question

I have two database - Database1 and Database2.

Both database contain a table that have similar structure as follows:

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

However, due to some reason, one of the tables in one of the database has data that is not exactly the same as the another table in the another database.

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

I tried using query it but nothing happen so was wondering if I have write the wrong query.

SQL

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.