比较两个表，并使用SQL Server过程在第3个表中插入具有添加或删除状态的所有记录 [英] Compare two tables and insert all records with added or removed status in 3rd table using SQL Server procedure

问题描述

我有表A和表B.我必须比较这些表记录，并使用以下格式的SQL Server过程将数据插入表C中

I have table A and table B . I have to compare this tables records and insert data to table C using SQL Server procedure in below format

表A

  name
  A
  B
  C
  D
  E
  F
  G

表B

  name
  A
  B
  Q
  C
  D
  F
  G

表c应该如下所示.它有一个额外的字段状态"以提及添加或删除记录.

table c should be like below. it has an extra field 'status' to mention record is added or removed.

name   status
A  
B
Q      newly added
C
D      
E      removed
F
G

我知道我们可以比较2个表并使用EXCEPT和UNION操作查找添加或删除的记录.但是在这种情况下，我必须将该记录与未更改的记录集成在一起，并且应该将添加或删除的记录放置在正确的位置.

I know we can compare 2 tables and find added or removed records using EXCEPT and UNION operations. But in this case, I have to integrate that records with unchanged records and should place that added or removed records in correct position.

推荐答案

根据您最终要完成的顺序，可以使用以下命令:

Depending on which order do you want to accomplish at the end you can use this:

select name, max(status), descr from(
select 
    coalesce(a.col, b.col) name,
    coalesce(a.descr, b.descr) descr,
    case
        when a.col is null then 'newly added'
        when b.col is null then 'removed'
    end status
    , ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) rn
from a a
left join b b on b.col = a.col
union
select 
    coalesce(a.col, b.col) name,
    coalesce(a.descr, b.descr) descr,
    case
        when a.col is null then 'newly added'
        when b.col is null then 'removed'
    end status
    , ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) rn
from b b
left join a a on b.col = a.col) A
group by name, descr
order by max(rn);

然后，如果要按表a中的顺序进行排序，则首先选择from b left join a，在第二个中选择from a left join b，如果要根据表b中的情况进行排序，请首先选择选择from a left join b，然后第二个选择from b left join a.

And then if you want to order by how it is in table a then in first select select from b left join a and in your second select from a left join b and if you want to oder by how it is in table b then in first select select from a left join b and in your second select from b left join a.

这是一个演示，其中包含最后请求的示例数据.

Here is a demo with the last requested samle data.

这篇关于比较两个表，并使用SQL Server过程在第3个表中插入具有添加或删除状态的所有记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！