Count the number of rows that do not match

Question

I have two tables with exactly the same structure, i.e. the same columns. I want to know the number of rows that exist in both tables, but do not have the same matching values ​​for all columns. For instance. The table shows the identifiers, name, country. The identifier is the primary key. If Id = 1 exists in both tables, then the other values ​​must also match. I am currently 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 ) 

The table has too many columns, so it becomes too cumbersome. Is there a better way to do this?

Answer 1

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

SQL Fiddle

Answer 2

Perhaps you can combine the columns into one field and check this equality?