I have a two tables that I want to compare using an EXCEPT clause to find out which data is different. This part of the problem is simple but the issue I am having is the two tables have about 50 columns each. Is there a way for me to only return the columns that the EXCEPT clause identified as different? This will save me having to go through the results manually. I ask because this will be a reoccurring task and I am trying to save myself time.
asked Mar 14 at 03:28 PM in Default
No, not with EXCEPT. What EXCEPT does is to compare two sets and return the members from one set which does not exist in the other set. What's returned is the whole member, not just some of the member attributes.
In databases, the set is a table (or with EXCEPT it's in fact a query which could be a table or the result of a query which mashes up tons of info from several tables).
If what you really want to do is to compare members to each other and find out which attributes are different, you'd have to compare members to each other using eg a JOIN. If you have key attributes you'd join on them, and then in the select list you could use something like CASE WHEN SetA.Attribute1 = SetB.Attribute1 THEN 0 ELSE 1 AS IsAttribute1Different.
If you wrap that query in eg a CTE, you could then add a WHERE clause which looks at rows where the sum of IsAttributeNDifferent > 0 to find which rows are different.
Something like this:
That would give you a result set with the key-column and ones and zeros to tell if the column is different or not. Save that in a temp-table or so and join it to the two tables to find out actual values from tables A and B.
answered Mar 15 at 01:00 PM