Capturing difference between 2 tables in one table-CodePudding

I have these two tables:

I want to capture in one table the difference between these 2 tables. Source table has changed columns for ID 1 and ID 3 with different statuses.

My expected table is like below:

I have tried to do it with MERGE function in T-SQL, but I can't do it correctly. In my real data I have about 60 columns with statuses, so I want to learn how to do it.

CodePudding user response：

here is one approach

drop table if exists #expected
create table #expected (
id int,
changedcolumn varchar(50),
dateofchange datetime);


with target_t as(
SELECT 1 as id , 'open' as status1 ,'open' as status2, 'closed' as status3 union all
SELECT 2 as id , 'closed' as status1 ,'open' as status2, 'open' as status3 union all
SELECT 3 as id , 'open' as status1 ,'open' as status2, 'closed' as status3 )

,source_t as (
SELECT 1 as id , 'open' as status1 ,'closed' as status2, 'open' as status3 union all
SELECT 2 as id , 'closed' as status1 ,'open' as status2, 'open' as status3 union all
SELECT 3 as id , 'open' as status1 ,'open' as status2, 'open' as status3 )


INSERT INTO #expected
SELECT tb.*
FROM(
    SELECT 
        t.id
        ,'Status1' as changedcolumn
        ,getdate() AS dateofchange
    FROM target_t t
    INNER JOIN source_t s
        ON t.id = s.id
    WHERE t.status1 <> s.status1
union all
    SELECT 
        t.id
        ,'Status2' as changedcolumn
        ,getdate() AS dateofchange
    FROM target_t t
    INNER JOIN source_t s
        ON t.id = s.id
    WHERE t.status2 <> s.status2
union all
    SELECT 
        t.id
        ,'Status3' as changedcolumn
        ,getdate() AS dateofchange
    FROM target_t t
    INNER JOIN source_t s
        ON t.id = s.id
    WHERE t.status3 <> s.status3
    ) tb
LEFT JOIN ( 
            SELECT * 
                ,row_number()over(partition by id, changedcolumn order by dateofchange desc) as rn
            FROM #expected 
             ) x
    ON x.id = tb.id
    AND x.rn = 1
WHERE x.id IS NULL