CODEWITHSUNDEEP
sqlsql-server
Vijay
Vijay

Reputation: 383

Compare two sql server tables

Can anyone suggest me how to compare two database tables in sql server and return the rows in the second table which are not in the first table. The primary key in both the tables is not the same. For instance, the tables are as follows.

Table1

ID             Name               DoB
1              John Doe           20/03/2012
2              Joe Bloggs         31/12/2011

Table2

ID             Name                DoB
11             John Doe            20/03/2012
21             Joe Bloggs          31/12/2011
31             James Anderson      14/04/2010

The sql query should compare only the Name and DoB in both tables and return 31 James Anderson 14/04/2010

Thanks.

Upvotes: 1

Views: 1663

Answers (5)

JohnH
JohnH

Reputation: 2133

This SQL statement compares two tables without having to specify column names.

SELECT 'Table1' AS Tbl, binary_checksum(*) AS chksum, * FROM Table1
WHERE binary_checksum(*) NOT IN (SELECT binary_checksum(*) FROM Table2)
UNION
SELECT 'Table2' AS Tbl, binary_checksum(*) AS chksum, * FROM Table2
WHERE binary_checksum(*) NOT IN (SELECT binary_checksum(*) FROM Table1)
ORDER BY <optional_column_names>, Tbl

The output will display any rows that are different and rows that are in Table1, but not Table2 and vice versa.

Upvotes: 0

Joe G Joseph
Joe G Joseph

Reputation: 24046

use CHECKSUM () function in sql server

select T1.* from Table1 T1 join Table2 T2
on CHECKSUM(T1.Name,T1.DOB)!= CHECKSUM(T2.Name,T2.DOB)

Details

Upvotes: 1

emurano
emurano

Reputation: 973

You want a LEFT OUTER JOIN. http://en.wikipedia.org/wiki/Join_(SQL)#Left_outer_join

This type of JOIN will return all records of the 'left' table (the table in the FROM clause in this example) even if there are no matching records in the joined table.

SELECT Table2.ID, Table2.Name, Table2.DoB
FROM Table2
LEFT OUTER JOIN Table1 ON Table1.Name = Table2.Name AND Table1.DoB = Table2.DoB
WHERE Table1.ID IS NULL

Note that you can substitue LEFT OUTER JOIN for LEFT JOIN. It's a short cut that most DBMSs use.

Upvotes: 1

Anthony Grist
Anthony Grist

Reputation: 38345

Pretty simple, use a LEFT OUTER JOIN to return everything from Table2 even if there isn't a match in Table1, then limit that down to only rows that don't have a match:

SELECT Table2.ID, Table2.Name, Table2.DoB
FROM Table2
LEFT OUTER JOIN Table1 ON Table2.Name = Table1.Name AND Table2.DoB = Table1.DoB
WHERE Table1.ID IS NULL

Upvotes: 5

Curtis
Curtis

Reputation: 103338

Look into the use of SQL EXCEPT

SELECT Name, DOB
FROM Table1
EXCEPT
SELECT Name, DOB
FROM Table2

http://msdn.microsoft.com/en-us/library/ms188055.aspx

Upvotes: 1

Related Questions