here's my best guess at what you want
drop table if exists #Sample_Delta_Procesing_C360_Loy
go
drop table if exists #LCMS_MRD_Delta_LoyaltyAccount_2
go
drop table if exists #Merged_RAW__LoyaltyAccount
go
drop table if exists #ActiveRC_Prep_LotaltyAccount
go
create table #Sample_Delta_Procesing_C360_Loy
(CDC_Type char(1),
PK_LOYALTYACCOUNT varchar(10),
COUNTRY_ID char(2),
ClassMark varchar(10))
create table #LCMS_MRD_Delta_LoyaltyAccount_2
(CDC_Type char(1),
PK_LOYALTYACCOUNT varchar(10),
COUNTRY_ID char(2),
ClassMark varchar(10) )
create table #Merged_RAW__LoyaltyAccount
(CDC_Type char(1),
PK_LOYALTYACCOUNT varchar(10),
COUNTRY_ID char(2),
ClassMark varchar(10),
IsDeleted bit)
create table #ActiveRC_Prep_LotaltyAccount
(CDC_Type char(1),
PK_LOYALTYACCOUNT varchar(10),
COUNTRY_ID char(2),
ClassMark varchar(10))
insert into #Sample_Delta_Procesing_C360_Loy (CDC_Type, PK_LOYALTYACCOUNT, COUNTRY_ID, ClassMark) values
('U','300021','DE','CRT'),
('N','51325','DE','Private'),
('D','50599','DE','Private'),
('U','300022','DE','CRT')
insert into #LCMS_MRD_Delta_LoyaltyAccount_2 (CDC_Type, PK_LOYALTYACCOUNT, COUNTRY_ID, ClassMark) values
('U','300021','DE','Private'),
('N','513215','DE','Private'),
('D','300022','DE','CRT')
;with cte as (
select orig.CDC_Type as orig_CDC_Type, orig.PK_LOYALTYACCOUNT as orig_PK_LOYALTYACCOUNT,
orig.COUNTRY_ID as orig_COUNTRY_ID, orig.ClassMark as orig_ClassMark,
loy2.CDC_Type as loy2_CDC_Type, loy2.PK_LOYALTYACCOUNT as loy2_PK_LOYALTYACCOUNT,
loy2.COUNTRY_ID as loy2_COUNTRY_ID, loy2.ClassMark as loy2_ClassMark
from #Sample_Delta_Procesing_C360_Loy orig
join #LCMS_MRD_Delta_LoyaltyAccount_2 loy2
on orig.CDC_Type = loy2.CDC_Type
and orig.PK_LOYALTYACCOUNT = loy2.PK_LOYALTYACCOUNT
and orig.COUNTRY_ID = loy2.COUNTRY_ID)
insert into #Merged_RAW__LoyaltyAccount (CDC_Type, PK_LOYALTYACCOUNT, COUNTRY_ID, ClassMark, IsDeleted)
select orig_CDC_Type as CDC_Type, orig_PK_LOYALTYACCOUNT as LOYALTYACCOUNT,
orig_COUNTRY_ID as COUNTRY_ID, orig_ClassMark as ClassMark, cast(0 as bit) as IsDeleted
from cte
union all
select loy2_CDC_Type as CDC_Type, loy2_PK_LOYALTYACCOUNT as LOYALTYACCOUNT,
loy2_COUNTRY_ID as COUNTRY_ID, loy2_ClassMark as ClassMark, cast(1 as bit) as IsDeleted
from cte
;with cte as (
select orig.CDC_Type as orig_CDC_Type, orig.PK_LOYALTYACCOUNT as orig_PK_LOYALTYACCOUNT,
orig.COUNTRY_ID as orig_COUNTRY_ID, orig.ClassMark as orig_ClassMark,
loy2.CDC_Type as loy2_CDC_Type, loy2.PK_LOYALTYACCOUNT as loy2_PK_LOYALTYACCOUNT,
loy2.COUNTRY_ID as loy2_COUNTRY_ID, loy2.ClassMark as loy2_ClassMark
from #Sample_Delta_Procesing_C360_Loy orig
join #LCMS_MRD_Delta_LoyaltyAccount_2 loy2
on orig.CDC_Type = loy2.CDC_Type
and orig.PK_LOYALTYACCOUNT = loy2.PK_LOYALTYACCOUNT
and orig.COUNTRY_ID = loy2.COUNTRY_ID)
insert into #ActiveRC_Prep_LotaltyAccount (CDC_Type, PK_LOYALTYACCOUNT, COUNTRY_ID, ClassMark)
select loy2_CDC_Type as CDC_Type, loy2_PK_LOYALTYACCOUNT as LOYALTYACCOUNT,
loy2_COUNTRY_ID as COUNTRY_ID, loy2_ClassMark as ClassMark
from cte
select * from #Merged_RAW__LoyaltyAccount
select * from #ActiveRC_Prep_LotaltyAccount