We help IT Professionals succeed at work.

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

pratikshahse
pratikshahse asked
on
Medium Priority
8,269 Views
Last Modified: 2011-08-18
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.

Comment
Watch Question

Database Consultant
CERTIFIED EXPERT
Top Expert 2009
Commented:
SELECT * FROM Table1 T1 WHERE NOT EXISTS(SELECT 1 FROM Table2 WHERE ZipCode = T1.ZipCode AND Name = T1.Name AND Address1=T1.Address1 AND State = t1.State and Custom1 = t1.Custom1 and custom4 = T1.Custome4 and City = t1.city and telNo = t1.telNo )
UNION ALL
SELECT * FROM Table2 T1 WHERE NOT EXISTS(SELECT 1 FROM  Table1  WHERE ZipCode = T1.ZipCode AND Name = T1.Name AND Address1=T1.Address1 AND State = t1.State and Custom1 = t1.Custom1 and custom4 = T1.Custome4 and City = t1.city and telNo = t1.telNo )

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
awking00Information Technology Specialist
CERTIFIED EXPERT

Commented:
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;
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 )



      

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
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.