SQL : Compare Two Datasets in SQL and highlight the columns those are different in another column.

Question

Hi All - I am comparing two tables and finding the discrepancy that one of the column is different in each of these tables.

Select NUMBER, cast(PROBLEM_FOUND as NVARCHAR) as PROBLEM_FOUND, CAST(DESCRIPTION as NVARCHAR) as CUSTOMERDESCRIPTION,

DURATION_DAYS, CREATION_CHANNEL, COUNT, OWNER_ID, TOTAL_DOWNTIME, CLOSE_DT, LAST_UPDATED_BY_ID, LAST_UPDATED_BY_ID, LAST_UPDATED_DT

from #SDSSR

EXCEPT

SELECT NUMBER, cast(PROBLEM_FOUND as NVARCHAR) as PROBLEM_FOUND, CAST(DESCRIPTION as NVARCHAR) as CUSTOMERDESCRIPTION,

DURATION_DAYS, CREATION_CHANNEL, COUNT, OWNER_ID, TOTAL_DOWNTIME, CLOSE_DT, LAST_UPDATED_BY_ID, LAST_UPDATED_BY_ID, LAST_UPDATED_DT

FROM #OraSR

The column NUMBER is the primary key here and its unique. I am seeing discrepancies.
For example the NUMBER = 12345, has the DURATION_DAYS = 1 in #SDSSR table and DURATION_DAYS = 10 in #OraSR table.

Is it possible to add in another column called 'Column differences' and add the columns which are different between these two tables ? In the example above the DURATION_DAYS column would show up in 'Column differences' column. There may also be differences of more than 3 columns as well and can these be comma separated in 'Column differences' column ?

The output would reflect like all the below columns and additional column 'Column differences'
NUMBER, PROBLEM_FOUND, CUSTOMERDESCRIPTION,DURATION_DAYS, CREATION_CHANNEL, COUNT, OWNER_ID, TOTAL_DOWNTIME, CLOSE_DT, LAST_UPDATED_BY_ID, LAST_UPDATED_BY_ID, LAST_UPDATED_DT, Column differences.

Thanks all in advance.

Answer 1

There is not any quick T-SQL solution here. You could do it with a FULL JOIN like this:

SELECT isnull(A.Number, B.Number), 
     A.Col1, B.Col1, IIF(A.Col1 IS DISTINCT FROM A.Col1, 'XXXX', '') AS Col1Diff,
   A.Col2, B.Col2, IIF(A.Col2 IS DISTINCT FROM A.Col2, 'XXXX', '') AS Col2Diff,
...
FROM  #SDSSR A
FULL JOIN #OraSR B ON A.NUMBER = B.NUMBER

But it is quite tedious. Even more so if you are not on SQL 2022 and cannot use IS DISTINCT FROM.

What I often do when I want to compare tables that ideally should be equal is that I copy and paste the result sets into Excel. The first table into Sheet1, the second into Sheet2. Use Shift-Ctrl-C so that you get column headers. Then in Sheet3, I enter this formula in A2: IIF(Sheet1!A2 = Sheet2!A2; "", "XXXX"). Then I select this cell and copy it to all other cells that covers the data in the other two sheets. Now where there is a difference there is an XXXX.

This will however not work well, if there different set of key values in the tables, since as soon as the ID goes out of sync, you will comparing values for different keys which is not very useful.

Answer 2

Hi @kkran

Please refer to this sample:

CREATE TABLE #TABLE1 (ID INT,COL1 VARCHAR(20),COL2 VARCHAR(20),COL3 VARCHAR(20))
CREATE TABLE #TABLE2 (ID INT,COL1 VARCHAR(20),COL2 VARCHAR(20),COL3 VARCHAR(20))

INSERT INTO #TABLE1 VALUES
(1,'AA','BB','CC'),(2,'DD','EE','FF'),(3,'GG','HH','II'),(4,'JJ','KK','LL'),(5,'MM','NN','OO')
INSERT INTO #TABLE2 VALUES
(1,'AAaa','BB','CC'),(2,'DD','EEee','FF'),(3,'GG','HH','IIii'),(4,'JJjj','KK','LLll'),(5,'MM','NN','OO')

SELECT * FROM #TABLE1;
SELECT * FROM #TABLE2;
 
SELECT T1.*,
       CONCAT(IIF(EXISTS(SELECT T1.COL1 EXCEPT SELECT T2.COL1 ), RTRIM('COL1;'),''),
              IIF(EXISTS(SELECT T1.COL2 EXCEPT SELECT T2.COL2 ), RTRIM('COL2;'),''),
              IIF(EXISTS(SELECT T1.COL3 EXCEPT SELECT T2.COL3 ), RTRIM('COL3;'),'')) AS COLUMN_DIFFERENCES
FROM #TABLE1 T1 JOIN #TABLE2 T2 ON T1.ID = T2.ID
WHERE EXISTS (SELECT T1.* EXCEPT SELECT T2.*)

DROP TABLE #TABLE1,#TABLE2

Best regards,

Cosmog Hong

---

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.