Counting the number of rows which do not match

Question

I have two tables with exactly the same structure i.e. same columns. I want to find out number of rows which exist in both tables but do not have exactly the same matching values for all columns. E.g. Table has colums Id, Name, Country. Id is the primary key. If Id=1 exists in both tables then other values should also match. Currently, I am using this kind of statement.

SELECT  COUNT(*)
FROM    ##Table1 t1 ,
        ##Table2 t2
WHERE   t1.Id = t2.Id
        AND ( t1.Name != t2.name
              OR t1.Country != t2.Country
            )  

Table has too many columns hence this is becoming too unwieldly. Is there a better way to do it?

Answer 1

SELECT COUNT(*)
FROM   ##Table1 t1
       JOIN ##Table2 t2
         ON t1.Id = t2.Id
            AND EXISTS (SELECT t1.*
                        EXCEPT
                        SELECT t2.*) 

SQL Fiddle