asked on

Compare two tables in SQL server 2005 and get whats not common

I have two almost similiar tables in sql server. I want to run a query to compare those two tables and to find out those rows that are not common. here are the columns for the two tables

Table 1

zipcode,
name,
address1,
state,
custom1,
custom4,
city,
tel no

Table 2

zipcode,
name,
address1,
state,
custom1,
custom4,
city,
tel no

in one row everything could be similiar except the tel no, in some other row everything could be similiar except the zip code. how do i run a query to figure out which row is not similiar.

ASKER CERTIFIED SOLUTION

Aneesh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

awking00

To get all not common records
select * from table1
minus
select * from table2
union
select * from table2
minus
select * from table1;

and to identify them -
select 'Table1', t1.* from table1 t1
minus
select 'Table1', t2.* from table2 t2
union
select 'Table2', t3.* from table2 t3
minus
select 'Table2', t4.* from table1 t4;

jay_gadhavi

In your table it shoud be one unique id field (empid or anything u want)....I used the unique id for the comparision.
Use this sql statement :

SELECT t1.empid,t1.zipcode, t1.telno, t1.name, t2.name AS table2_name, t2.phoneno AS table2_phoneno, t2.zipcode
FROM dbo.table1 t1 INNER JOIN
dbo.Table2 t2 ON t1.empid = t2.empid AND t1.telno <> t2.telno
union
SELECT t1.empid,t1.zipcode, t1.phoneno, t1.name, t2.name AS table2_name, t2.phoneno AS table2_phoneno, t2.zipcode
FROM dbo.table1 t1 INNER JOIN
dbo.Table2 t2 ON t1.empid = t2.empid AND t1.zipcode <> t2.zipcode

(Note : After run this sql statement i got all the rows which telno and zipcode is different )

prajapati84

If u want to get the records where only zipcode and tel_no are not similar and other field are similar then try the first query. But If you want the records where every column must be checked whether they are similar or not, you must be having a common field like ID or anything else to join both tables. I have used here ID column to join both tables, u need to replace ur common field with ID. For this solution, try the second query.

First Query:
(select t1.*,t2.* from table1 t1,table2 t2 where t1.name=t2.name and t1.address1=t2.address1 and t1.state=t2.state and t1.custom1=t2.custom1 and t1.custom4=t2.custom4 and t1.custom4=t2.custom4 and t1.zipcode=t2.zipcode and
t1.tel_no<>t2.tel_no) union (select t1.*,t2.* from table1 t1,table2 t2 where t1.name=t2.name and t1.address1=t2.address1 and t1.state=t2.state and t1.custom1=t2.custom1 and t1.custom4=t2.custom4 and t1.custom4=t2.custom4 and t1.zipcode<>t2.zipcode and t1.tel_no=t2.tel_no)

Second Query:
(select t1.*,t2.* from table1 t1,table2 t2 where t1.id=t2.id and t1.telno<>t2.telno)
union (select t1.*,t2.* from table1 t1,table2 t2 where t1.id=t2.id and t1.zipcode<>t2.zipcode)

Regards,
Mukesh