x

Find Columns Difference With EXCEPT Clause

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.

more ▼

asked Mar 14 at 03:28 PM in Default

avatar image

DBANovice
150 1 1 8

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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:

 WITH CTE AS (
   SELECT 
     A.KeyColumn,
     CASE WHEN A.Attribute1=B.Attribute1 THEN 0 ELSE 1 AS IsAttribute1Different,
     CASE WHEN A.Attribute2=B.Attribute2 THEN 0 ELSE 1 AS IsAttribute2Different,
     ...
   FROM TableA A INNER JOIN TableB B ON A.KeyColumn = B.KeyColumn
 )SELECT * FROM CTE WHERE IsAttribute1Different + IsAttribute2Different + ... + ISAttributeNDifferent > 0

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.

more ▼

answered Mar 15 at 01:00 PM

avatar image

Magnus Ahlkvist
22.5k 20 44 43

Oh, and do not forget to handle NULLs if they are allowed. Because NULL = NULL will not evaluate to TRUE, it will evaluate to Unknown, which is not true and therefore the attributes will in my query be reported as different.

Mar 15 at 01:01 PM Magnus Ahlkvist

@DBANovice I would like to add that in addition to what @Magnus Ahlkvist already said, the EXCEPT will not handle inserts and deletes like you would probably like. Please run this to see what I mean:


declare @a table (ID int, Val char(1));
declare @b table (ID int, Val char(1));
insert into @a values (1, 'a'), (2, 'b');
insert into @b values (1, 'a'), (2, 'b');
-- nothing is returned, data is identical
select * from @a except select * from @b;
-- ID          Val
-- ----------- ----
-- update one row in b
update @b set Val = 'd' where ID = 1;
-- insert one row in a
insert into @a values (4, 'f')
-- The rows from a with ID in (1, 4) are returned: ID = 1 has been 
-- modified in b, and ID = 4 is a new row in a.
-- There is no way to know why exactly the rows appear in the results
select * from @a except select * from @b;
-- ID          Val
-- ----------- ----
-- 1           a
-- 4           f
-- Insert one row in b to see that even though the row has been inserted in b 
-- it is not included in the results
insert into @b values (5, 'g')
select * from @a except select * from @b;
-- ID          Val
-- ----------- ----
-- 1           a
-- 4           f
Mar 15 at 02:18 PM Oleg

Thanks I appreciate it I will give this a try

Mar 16 at 12:03 AM DBANovice
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x461
x454
x428

asked: Mar 14 at 03:28 PM

Seen: 32 times

Last Updated: Mar 16 at 12:03 AM

Copyright 2018 Redgate Software. Privacy Policy