Comparison Query to Compare Two SQL Server Tables

Question

I would like to know how to compare two different database table records. What I mean is I will compare two database tables which may have different column names but same data. But one of them may have more records than the other one so I want to see what the difference is between those two tables. To do that how to write the sql query ? FYI : these two databases are under the same SQL Server instance.

Table1
------+---------
|name |lastname|
------+---------
|John |rose    |
------+---------
|Demy |Sanches |
------+---------

Table2
------+----------
|name2|lastname2|
------+----------
|John |rose     |
------+----------
|Demy |Sanches  |
------+----------
|Ruby |Core     |
------+----------

Then when after comparing table 1 and table 2, it should return Ruby Core from Table2.

Solution 1

If you do an outer join from T1 to T2 you can find rows in the former that are not in the latter by looking for nulls in the T2 values, similarly an outer join of T2 to T1 will give you rows in T2. Union the two together and you get the lot... something like:

SELECT 'Table1' AS TableName, name, lastname FROM
    Table1 OUTER JOIN Table2 ON Table1.name = Table2.name2 
                             AND Table1.lastname = Table2.lastname
WHERE Table2.name2 IS NULL
UNION
SELECT 'Table2' AS TableName, name2 as name, lastname2 as lastname FROM
    Table2 OUTER JOIN Table1 ON Table2.name2 = Table1.name 
                             AND Table2.lastname2 = Table1.lastname
WHERE Table1.name IS NULL

That's off the top of my head - and I'm a bit rusty :)

Solution 2

Select * from Table1
Except
Select * from Table2

It will show all mismatch records between table1 and table2

Solution 3

Late answer but can be useful to other readers of this thread

Beside other solutions, I can recommend SQL comparison tool called ApexSQL Data Diff.

I know you'd prefer the solution not based on the software, but for other visitors, who may want to do this in an easier way, I strongly suggest reading this article: http://solutioncenter.apexsql.com/how-to-compare-sql-server-database-tables-with-different-names/

The article explains how to use the Object mapping feature in ApexSQL Data Diff, which is particularly useful in situations where two tables share the same name, but their column names are different.

To handle such a case - each column pair needs to be mapped manually in order for the data stored within them to be included when comparing SQL database tables for differences.

Solution 4

If you are using Sql server use a full join. it does exactly the same as Murph said but in one command.

    SELECT 'Table1' AS TableName, name, lastname 
    FROM Table1 
FULL JOIN Table2 ON Table1.name = Table2.name2 
                                 AND Table1.lastname = Table2.lastname

Solution 5

You could use the CHECKSUM function if you're confident that the data is expressed identically.

Example:

if not OBJECT_ID('Table1', 'Table') is null drop table Table1
if not OBJECT_ID('Table2', 'Table') is null drop table Table2
create table table1
( id int identity(0, 1), 
   name varchar(128), 
   lastname varchar(128)
)
create table table2
( id int identity(0, 1), 
   name varchar(128), 
   lastname varchar(128)
)
insert into table1 (name, lastname) values ('John', 'rose')
insert into table1 (name, lastname) values ('Demy', 'Sanches')
insert into table2 (name, lastname) values ('John', 'rose')
insert into table2 (name, lastname) values ('Demy', 'Sanches')
insert into table2 (name, lastname) values ('Ruby', 'Core')

select 
    table2.*
from table1
     right outer join table2 on CHECKSUM(table1.name, table1.lastname) = CHECKSUM(table2.name, table2.lastname)
where table1.id is null

See the CHECKSUM MSDN topic for more information.

View more solutions

Comments

[Tarik]

Let me try your method. Thanks.

[forsvarir]

You should consider elaborating on your answer by providing some details about what it is you're doing and how it answers the OP's question.

[davidXYZ]

Short and sweet (and effective). Exactly what I wanted. +1

[slartidan]

Consider using union like this: (Select * from Table1 Except Select * from Table2) UNION (Select * from Table2 Except Select * from Table1). This will give you any deleted or added rows in both of the tables.

[Nicolas Vieira]

IMO slartidan's union comment is required for a complete answer. Without the union your result set will only contain values from table1 that are not in table2. Adding the union will give you records missing from both tables (as he stated).