Sample Header Ad - 728x90

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

3 votes
4 answers
21965 views
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
Asked by Jack (2567 rep)
Jan 8, 2013, 05:48 AM
Last activity: Jun 4, 2020, 12:46 PM