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.