SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

SELECT * INTO #TABLEA
FROM 
( VALUES ('A','desc A'),('B','desc B'),('C','desc C'),('D','desc D'),('E','desc E'),('F','desc F'),('G','desc G'),('H','desc H'),('I','desc I'),('J','desc J')
) DS ([name],[description])
10 rows affected

SELECT * INTO #TABLEB
FROM 
( VALUES ('A','desc A'),('B','desc B'),('M','desc M'),('N','desc N'),('O','desc O'),('C','desc C'),('D','desc D'),('E','desc E'),('G','desc G'),('H','desc H'),('J','desc J')
) DS ([name],[description])
11 rows affected

with tablebrow as(
select *,row_number() over (order by (select NUll)) rn from #tableb
),
tablearow as(
select *,row_number() over (order by (select NUll)) rn from #tablea
)
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 tablearow a
full join tablebrow b on b.name = a.name 
order by isnull(b.rn,(select tr.rn from tablebrow tr where description = (select TOP 1 description from tablearow t where t.rn < a.rn order by t.rn desc) ))


name
description
status


A
desc A
null

B
desc B
null

M
desc M
newly added

N
desc N
newly added

O
desc O
newly added

C
desc C
null

D
desc D
null

E
desc E
null

F
desc F
removed

G
desc G
null

H
desc H
null

I
desc I
removed

J
desc J
null

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