Skip to content
Advertisement

Compare two tables and combine records with status in SQL Server procedure

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

table A

id   name   description
 1    A      desc A
 2    B      desc B
 3    C      desc C
 4    D      desc D
 5    E      desc E
 6    F      desc F
 7    G      desc G
 8    H      desc H
 9    I      desc I
 10   J      desc J 

table B

id   name  description
 1    A        desc A
 2    B        desc B
 3    M        desc M
 4    N        desc N
 5    O        desc O
 6    C        desc C
 7    D        desc D
 8    E        desc E
 9    G        desc G
 10   H        desc H
 11   I        desc I
 12   J        desc J

Expected output is like below. It has an extra field ‘status’ to mention record is added or removed.

name  description  status
A        desc A
B        desc B   
M        desc M    newly added
N        desc N    newly added
O        desc O    newly added
C        desc C
D        desc D
E        desc E
F        desc F    removed
G        desc G
H        desc H
I        desc I
J        desc J

I tried code like below

  select 
     coalesce(a.name, b.name) name,
     coalesce(a.description, b.description) description,
   case
      when a.name is null then 'newly added'
      when b.name is null then 'removed'
   end status
     from tablea a
     full join tableb b on b.name = a.name

But in output, newly added records M,N,O is coming in last position.. M,N,O should be under B as in input table. I am not getting output in order as mentioned in expected output.

Advertisement

Answer

As requested in other question, I have updated my answer to add your identity column instead of row_number()

SELECT COALESCE(a.NAME, b.NAME) NAME, 
       COALESCE(a.description, b.description) description, 
       CASE 
         WHEN a.NAME IS NULL THEN 'newly added' 
         WHEN b.NAME IS NULL THEN 'removed' 
       END status 
FROM   a FULL JOIN b ON b.NAME = a.NAME 
ORDER  BY ISNULL(b.id, (SELECT sb.id 
                        FROM   b sb 
                        WHERE  description = (SELECT TOP 1 description 
                                              FROM   a sa 
                                              WHERE sa.id < a.id 
                                              ORDER  BY sa.id DESC))) 

CHECK DEMO HERE

Note: You can use name instead of description in the subquery based on your need.

User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement