7

This question is in the same vein as this question, but just slightly different.

I have 2 tables, TableA and TableB where TableB is really just a backup of TableA, the only real difference is that TableB has an "ACTION" column that indicates the action that had been taken in TableA. For now I am ignoring that column (although ideally I would want it appended to the final results, so please feel free to suggest on that too :D )

I want to compare the two tables and get the rows from each table that are different. I've used Jeff's SQL Server Blog - The shortest, fastest, and easiest way to compare two tables in SQL Server: UNION ! example to create a query, however, it returns only the rows from TableB that are different.

What is the recommended approach?

Note: the code is exactly like the example in the second link, so I won't post any of it here

Community
  • 1
  • 1
Emmanuel F
  • 1,125
  • 1
  • 15
  • 34
  • Are you sure that there are rows in Table A that are not in Table B? Can you provide a simplified set of data that illustrates the problem? – Tom H Jun 22 '11 at 16:54
  • There should *not* be any rows in TableA that are not in TableB, but that is acceptable. The only thing I want to do right now is compare the rows in TableA and TableB. – Emmanuel F Jun 22 '11 at 18:05

1 Answers1

8

This will give you everything in A that's not in B

select * from tableA
Except
select * from tableB

and vice-versa

select * from tableB
Except
select * from tableA

Edit: Joined this way:

(select * from tableA
Except
select * from tableB)
union all
(select * from tableB
Except
select * from tableA)
Bob Probst
  • 9,533
  • 8
  • 32
  • 41
  • I tried this as is, unfortunately it listed two different views for me in MS SQL Server Mgt Studio. I'm not familiar enough with SQL to merge the two results together. Although I have a feeling it's going to be ridiculously simple. – Emmanuel F Jun 22 '11 at 20:20