views:

45

answers:

2

I would like to make an sql query to compare two tables with identical columns, both names and types. Each table has a unique key. I want the query to return any rows that contain unequal values. I know could do something like this

select * 
from table_1, table_2
where 
table_1.key = table_2.key
and (
 table_1.col1 != table_2.col1 OR
 table_1.col2 != table_2.col2 OR
 ...

)

but this would be tedious since there are a large and potentially variable number of columns.

edit

If it helps, I'm using a tsql system.

+2  A: 

Not sure what type of DB you are using but if you are using SQL Server 2005 or higher try this:

select 'table1' as tblName, *  from
  (select * from table1
   except
   select * from table2) x
union all
select 'table2' as tblName, *  from
  (select * from table2
   except select * 
   from table1) x
Abe Miessler
+1  A: 

How abt this..

select * from table1 where not exists (select * from table2)
union all
select * from table2 where not exists (select * from table1)
Gourav C