SQL Server 2014

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


(No column name)


Microsoft SQL Server 2014 (SP3-CU-GDR) (KB4535288) - 12.0.6372.1 (X64) 
 Dec 12 2019 15:14:11 
 Copyright (c) Microsoft Corporation
 Express Edition (64-bit) on Windows NT 6.3 <X64> (Build 17763: ) (Hypervisor)



create table source_excel(id varchar(10),name varchar(10),City_Scope varchar(50),flag varchar(10) );

insert into source_excel values(101,'Plate','NY|TN','Ready');
insert into source_excel values(102,'Nut','NY|TN','Sold');
insert into source_excel values(103,'Ring','TN|MC','Planning');
insert into source_excel values(104,'Glass','NY|TN|MC','Ready');
insert into source_excel values(105,'Bolt','MC','Expired');
5 rows affected

select * from source_excel


id
name
City_Scope
flag


101
Plate
NY|TN
Ready

102
Nut
NY|TN
Sold

103
Ring
TN|MC
Planning

104
Glass
NY|TN|MC
Ready

105
Bolt
MC
Expired


create table source_dw(id varchar(10),name varchar(10),City varchar(50),flag varchar(10) );

insert into source_dw values(101,'Plate','NY','Ready');
insert into source_dw values(101,'Plate','TN','Ready');
insert into source_dw values(102,'Nut','TN','Expired');
insert into source_dw values(103,'Ring','MC','Planning');
insert into source_dw values(104,'Glass','MC','Ready');
insert into source_dw values(104,'Glass','NY','Expired');
insert into source_dw values(105,'Bolt','MC','Expired');
7 rows affected

select * from source_dw


id
name
City
flag


101
Plate
NY
Ready

101
Plate
TN
Ready

102
Nut
TN
Expired

103
Ring
MC
Planning

104
Glass
MC
Ready

104
Glass
NY
Expired

105
Bolt
MC
Expired


CREATE FUNCTION [dbo].[DelimitedSplit8K_LEAD]
--===== Define I/O parameters
        (@pString VARCHAR(8000), @pDelimiter CHAR(1))
RETURNS TABLE WITH SCHEMABINDING AS
 RETURN
--===== "Inline" CTE Driven "Tally Table” produces values from 0 up to 10,000...
     -- enough to cover VARCHAR(8000)
  WITH E1(N) AS (
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
                ),                          --10E+1 or 10 rows
       E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
       E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
 cteTally(N) AS (--==== This provides the "zero base" and limits the number of rows right up front
                     -- for both a performance gain and prevention of accidental "overruns"
                 SELECT 0 UNION ALL
                 SELECT TOP (DATALENGTH(ISNULL(@pString,1))) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
                ),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
                 SELECT t.N+1
                   FROM cteTally t
                  WHERE (SUBSTRING(@pString,t.N,1) = @pDelimiter OR t.N = 0) 
                )
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
 SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY s.N1),
        Item = SUBSTRING(@pString,s.N1,ISNULL(NULLIF((LEAD(s.N1,1,1) OVER (ORDER BY s.N1) - 1),0)-s.N1,8000))
   FROM cteStart s
select a.id,a.name,split.item,a.flag
into #Excel from source_excel a 
CROSS APPLY dbo.DelimitedSplit8K_LEAD(a.city_scope,'|') split
10 rows affected

select id,'Yes' as City_Missing  into #City_missing from #excel excel
where NOT EXISTS(SELECT 1 FROM Source_dw dw WHERE dw.ID=excel.ID and dw.name=excel.name and excel.item=dw.city and dw.flag=excel.flag )

5 rows affected

select excel.id,'Yes' as Flag_Mismatch into #Flag_mismatch from #Excel excel join Source_dw dw on excel.id=dw.id and dw.name=excel.name and excel.item=dw.city  and dw.flag!=excel.flag
2 rows affected

select distinct excel.id,city.City_Missing,flag.Flag_Mismatch from source_excel excel left join #city_missing city on excel.id=city.id
left join #Flag_mismatch flag on flag.id=excel.id


id
City_Missing
Flag_Mismatch


101
null
null

102
Yes
Yes

103
Yes
null

104
Yes
Yes

105
null
null
id	name	City_Scope	flag
101	Plate	NY\|TN	Ready
102	Nut	NY\|TN	Sold
103	Ring	TN\|MC	Planning
104	Glass	NY\|TN\|MC	Ready
105	Bolt	MC	Expired
id	City_Missing	Flag_Mismatch
101	null	null
102	Yes	Yes
103	Yes	null
104	Yes	Yes
105	null	null