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.

Answer 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 :)

Answer 2

Firefly will do exactly what you're looking for. It lets you build two sql statements then compare the results of the sql queries showing missing rows and data differences. Each query can even come from a different database like oracle / sql server.

http://download.cnet.com/Firefly-Data-Compare-Tool/3000-10254%5F4-10633690.html?tag=mncol

Answer 3

Try dbForge Data Compare for SQL Server. It can compare and synchronize any database data. Quick, easy, always delivering a correct result. See how it flies on your database!

Answer 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

Answer 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.