Compare and display multiple Columns in 2 different tables in SQL

1

I am performing a matching of 2 different tables. They have the same columns For the research the Query must go through all the data and compare the data in Table a and table b and show the number of cell mismatches and the actual cell where there is a mismatch At this moment I have a Query using the When statement….

CASE

    WHEN TABLEA.COL1 <> TABLEB.COL1
    AND     TABLEA.COL2 <> TABLEB.COL2
    AND     TABLEA.COL3 = TABLEB.COL3
    AND     TABLEA.COL4 <> TABLEB.COL4
    AND     TABLEA.COL5 = TABLEB.COL5
    AND     TABLEA.COL6 = TABLEB.COL6
    AND     TABLEA.COL7 = TABLEB.COL7
    AND     TABLEA.COL8 <> TABLEB.COL8
    THEN ‘4 FIELDS – COL1 & COL2 & COL4 & COL8’

This is not efficient. Is there any statement that can go through the data automatically and achieve this?

2

I reckon you are going to have to do something like that

My approach would be to do:

NullIf(
    CASE WHEN TABLEA.COL1 <> TABLEB.COL1 THEN 'COL1 &' ELSE '' END
    + CASE WHEN TABLEA.COL2 <> TABLEB.COL2 THEN 'COL2 &' ELSE '' END
    ...
    , '') AS DifferentColumns
```
to concatenate the results of the comparisons into the "Message" then only included non-NULL [DifferentColumns] in the report. If you, also, need the number of such different columns you could count the "&" in the message, instead of (separately) evaluating a count of the number of differences.
3

P.S. we make the "<>" comparison using a BINARY collation so that we also show up a difference in Upper / Lower case, and we also compare the DATALENGTH of the columns in order to also catch any difference in trailing spaces (an equals test will ignore trailing spaces in the comparison)