修改SQL脚本架构后无法检测缺失文件求助

需要检测临时表#temp2中的文件名（例如Radiology_PaidClaimAudit_Member_Count_20240129.txt）是否匹配INSERT语句里的文件掩码。原脚本在dm_Caresrc架构下运行正常，能返回缺失的2个文件掩码，但修改架构及文件掩码后，即便#temp2表中有文件也无结果返回。添加IF语句是为了避免#temp2为空时报错（这种情况时常发生），怀疑是关联逻辑问题导致无法正确匹配新值。

原代码 #

declare @monitor_day as int
declare @expected_file_mask as VARCHAR(80)
DECLARE @source_extract varchar(8)
DECLARE @target_extract varchar(8)
set @monitor_day = '1' ---INPUT Expected Day of receipt

SELECT  [sourcefile],
        [start_time],
        [target_table],
        DAY(start_time) as dayofmonth
        --substring(right(sourcefile,15),1,19) as source_extract,
        --substring(right(target_table,6),1,6) as target_extract
FROM [dm_Caresrc].[dbo].[TablesProcessed] with (nolock)
where DAY(start_time) <= @monitor_day-32

 CREATE TABLE #temp (
       Missing_File_mask varchar(255)
 );

INSERT INTO #temp (Missing_File_mask) VALUES ('Radiology_PaidClaimAudit_PaidClaim');
INSERT INTO #temp (Missing_File_mask) VALUES ('Radiology_PaidClaimAudit_Member');
INSERT INTO #temp (Missing_File_mask) VALUES ('Radiology_PaidClaimAudit_Provider');

CREATE TABLE #temp2 (
       sourcefile1 varchar(255)
 );

 INSERT INTO #temp2 (sourcefile1)
   select sourcefile
   FROM [dm_Caresrc].[dbo].[TablesProcessed] with (nolock)

 if (select count(*) from #temp2) > 0
 begin
   Select distinct(Missing_File_mask) 
   from #temp a 
   Inner Join #temp2 b on B.sourcefile1 not like (A.Missing_File_mask+ '%')
 end
 else
 begin
    Select distinct(Missing_File_mask) from #temp
 end

 Drop table #temp
 Drop table #temp2

问题根源 #

原脚本里的INNER JOIN逻辑完全错误：用B.sourcefile1 not like (A.Missing_File_mask+ '%')做关联条件时，只要有任意一个文件名不匹配某个掩码，这个掩码就会被返回，而不是所有文件名都不匹配该掩码时才判定为缺失。这种逻辑在原架构下可能碰巧得到正确结果，但换架构或掩码后，只要有一个文件匹配掩码，关联结果就会重复，最终DISTINCT可能把正确结果过滤掉，导致无输出。另外，开头的SELECT语句完全冗余，没有实际作用。

修正后的代码 #

DECLARE @monitor_day AS INT
DECLARE @schema_name NVARCHAR(128) = '你的目标架构名' -- 替换为实际要使用的架构
SET @monitor_day = 1 ---INPUT Expected Day of receipt

-- 创建存储预期文件掩码的临时表
CREATE TABLE #temp (
    Missing_File_mask VARCHAR(255)
);

INSERT INTO #temp (Missing_File_mask) 
VALUES 
('新的文件掩码1'), -- 替换为你的目标文件掩码
('新的文件掩码2'),
('新的文件掩码3');

-- 从目标架构的永久表中获取符合时间条件的已处理文件
CREATE TABLE #temp2 (
    sourcefile1 VARCHAR(255)
);

INSERT INTO #temp2 (sourcefile1)
SELECT sourcefile
FROM [@schema_name].[dbo].[TablesProcessed] WITH (NOLOCK)
WHERE DAY(start_time) <= @monitor_day - 32; -- 保留原时间过滤规则

-- 判断并返回缺失的文件掩码
IF (SELECT COUNT(*) FROM #temp2) > 0
BEGIN
    -- 只返回那些没有任何匹配文件的掩码
    SELECT t.Missing_File_mask
    FROM #temp t
    WHERE NOT EXISTS (
        SELECT 1 
        FROM #temp2 f
        WHERE f.sourcefile1 LIKE t.Missing_File_mask + '%'
    )
END
ELSE
BEGIN
    -- 当没有任何文件时，返回所有预期掩码
    SELECT Missing_File_mask FROM #temp
END

DROP TABLE #temp
DROP TABLE #temp2

关键修改说明 #

• 替换错误的INNER JOIN为NOT EXISTS：只有当#temp2里完全找不到匹配当前掩码的文件时，才将该掩码标记为缺失，逻辑完全符合需求。
• 新增@schema_name变量，修改架构时只需改动这一处，避免多处修改出错。
• 删除冗余的开头SELECT语句，精简脚本结构。
• 保留原有的IF判断逻辑，防止#temp2为空时出现异常。

内容的提问来源于stack exchange，提问作者BrianA