How to compare data in cells in 2 different tables in SQL Server?

Question

I have 2 identical tables. In one of the tables the value of the cell in any row can change, the other table is constant. How can I find out which value has changed in which row?

For example, I have Table1 (screenshot #1) which is constant and Table2 (screenshot #2) in which value of any cell in any row can change. I want to detect the change and know what value it changed to. Number of rows and columns will remain same in both the tables.

How do I compare these 2 tables as shown in the screenshots below to find out the difference?

question from:https://stackoverflow.com/questions/66057119/how-to-compare-data-in-cells-in-2-different-tables-in-sql-server

Answer 1

One way could be to UNPIVOT both tables and then inner JOIN. In this case I changed the value in #tempA at row=3, col='a' from 111 to 11. The query returns any differences

drop table if exists #tempA;
go
create table #tempA(
  a      int not null,
  b      int not null,
  c      int not null);

insert #tempA values
(1, 2, 3),
(11, 22, 33),
(11, 222, 333);

drop table if exists #tempB;
go
create table #tempB(
  a      int not null,
  b      int not null,
  c      int not null);

insert #tempB values
(1, 2, 3),
(11, 22, 33),
(111, 222, 333);

with
a_cte(a, b, c, rn) as (
    select *, row_number() over (order by a) 
    from #tempA),
b_cte(a, b, c, rn) as (
    select *, row_number() over (order by a) 
    from #tempB),
a_unpvt_cte as (
    select v.* 
    from a_cte a
         cross apply (values ('a', a, rn), ('b', b, rn), ('c', c, rn)) v(col, letter, rn)),
b_unpvt_cte as (
    select v.* 
    from b_cte a
         cross apply (values ('a', a, rn), ('b', b, rn), ('c', c, rn)) v(col, letter, rn))
select a.col, a.rn, a.letter a_letter, b.letter b_letter
from a_unpvt_cte a
     join b_unpvt_cte b on a.col=b.col
                           and a.rn=b.rn
where
  a.letter<>b.letter;

col rn  a_letter    b_letter
a   3   11          111