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)))
Note: You can use name instead of description in the subquery based on your need.