Help in Compare two tables for any difference

Question

Hello- I want to compare two table's data and if found any difference in any column then these only want to show in the result, as showed in the Expected Result. They key of mapping is Tname, Code and PerID. All columns except key columns (Tname, Code, PerID) are showing in the result to see the difference. If data is matching then should should blank. Create table TableA (TName varchar(20), Code varchar(20), PerID varchar(20), City Varchar(20), Post Varchar(10), Add Varchar(20)) Insert into TableA values ('AAAA','AA','001','HH','M01','121 Cres') Insert into TableA values ('BBBB','AD','002','DD','N01','631 Dr') Create table TableB (TName varchar(20), Code varchar(20), PerID varchar(20), City varchar(20), Post varchar(10), Add varchar(20)) Insert into TableB values ('AAAA','AA','001',GG','M01','121 Cres') Insert into TableB values ('BBBB','AD','002','KK','K01','631 Dr') Expected Result: Tname Code PerID City_A CityB PostA Post_B AddA AddB AAAA AA 001 HH GG BBBB AD 002 DD KK N01 K01

Answer 1

Hi @Mike

select A.TName,A.Code,A.PerID,
       case when A.City = B.City then '' else A.City end as City_A,
	   case when A.City = B.City then '' else B.City end as City_B,
	   case when A.Post = B.Post then '' else A.Post end as Post_A,
	   case when A.Post = B.Post then '' else B.Post end as Post_B,
	   case when A.[Add] = B.[Add] then '' else A.[Add] end as Add_A,
       case when A.[Add] = B.[Add] then '' else B.[Add] end as Add_B
from TableA as A inner join TableB as B on A.TName = B.TName

Output:

Best regards, Percy Tang

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Here is a quick generic query to compare to tables with the same set of columns (and same column order!). The query returns all rows there there are differences.

SELECT *
FROM  A
FULL JOIN B ON A.keycol1 = B.keycol1 AND A.keycol2 = B .keycol2 ...
WHERE NOT EXISTS (SELECT A.* INTERSECT SELECT B.*)

It does not give you the format you want, but I started there since it's short can concise. Although, if you want to exempt column from the comparison, you will need replace A.* and B.* with column lists. For your desired result, the SELECT list will have to be more long-winding like:

SELECT isnull(A.keycol1, B.keycol1) AS keycol1, isnull(A.keycol2, B.keycol2), ...
       CASE WHEN NOT EXISTS (SELECT A.plaincol1 INTERSECT SELECT B.plaincol1) THEN A.plaincol1 END AS "A.plaincol1",
       CASE WHEN NOT EXISTS (SELECT A.plaincol1 INTERSECT SELECT B.plaincol1) THEN B.plaincol1 END AS "B.plaincol1",
       CASE WHEN NOT EXISTS (SELECT A.plaincol2 INTERSECT SELECT B.plaincol2) THEN A.plaincol2 END AS "A.plaincol2",
       CASE WHEN NOT EXISTS (SELECT A.plaincol2 INTERSECT SELECT B.plaincol2) THEN B.plaincol2 END AS "B.plaincol2",
       ...
FROM A
FULL JOIN B ON A.keycol1 = B.keycol1 AND A.keycol2 = B .keycol2 ...
WHERE NOT EXISTS (SELECT A.* INTERSECT SELECT B.*)

The point with INTERSECT here is that it also will handle the case when the column in A is NULL and the column in B is not. For a longer discussion about this, see this blogpost of mine: https://sqlservergeeks.com/a-tip-about-intersect/

Answer 3

Hi @Mike
If I understand correctly, you can try this query.

select A.TName,A.Code,A.PerID,
       case when A.City = B.City then null else A.City end as City_A,
	   case when A.City = B.City then null else B.City end as City_B,
	   case when A.Post = B.Post then null else A.Post end as Post_A,
	   case when A.Post = B.Post then null else B.Post end as Post_B,
	   case when A.[Add] = B.[Add] then null else A.[Add] end as Add_A,
       case when A.[Add] = B.[Add] then null else B.[Add] end as Add_B
from TableA as A inner join TableB as B on A.TName = B.TName

Output:

Best regards, Percy Tang

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.