使用SQL去除服务器名称后缀问题求助（MERGE语句未生效）

问题原因 #

你的MERGE语句仅在**匹配已有记录（WHEN MATCHED）时对computer_name做了截断处理，但在插入新记录（WHEN NOT MATCHED BY Target）**时直接插入了原始的Source.computer_name值。由于你先执行了DELETE FROM clean_tanium_server;，第一次同步所有记录都是新插入的，完全没触发匹配更新逻辑，因此所有服务器名称都保留了后缀。

另外存在隐藏风险：如果有不带点号的服务器名称，CHARINDEX('.', Source.computer_name)会返回0，LEFT(..., 0-1)会因参数为负抛出错误。

解决步骤 #

1. 统一两个分支的computer_name处理逻辑 #

在插入和更新分支都使用相同的截断逻辑，同时处理无点号的边界情况：

修正后的MERGE代码

DELETE FROM clean_tanium_server;
MERGE clean_tanium_server AS Target
USING tanium_server AS Source
ON Source.computer_id = Target.computer_id AND Source.[ci_installed_application name] = Target.application_name
WHEN NOT MATCHED BY Target THEN
    INSERT (computer_id, computer_name, operating_system, application_name, application_normalized_name, chassis_type, cpu_core, cpu_processor, ip_address)
    VALUES (
        Source.computer_id,
        -- 兼容带点号和不带点号的服务器名称
        CASE 
            WHEN CHARINDEX('.', Source.computer_name) > 0 
            THEN LEFT(Source.computer_name, CHARINDEX('.', Source.computer_name) - 1)
            ELSE Source.computer_name
        END,
        Source.operating_system,
        Source.[ci_installed_application name],
        Source.[ci_installed_application normalized_name],
        Source.chassis_type,
        Source.cpu_core,
        Source.cpu_processor,
        Source.ip_address
    )
WHEN MATCHED THEN UPDATE SET
    Target.computer_name = CASE 
            WHEN CHARINDEX('.', Source.computer_name) > 0 
            THEN LEFT(Source.computer_name, CHARINDEX('.', Source.computer_name) - 1)
            ELSE Source.computer_name
        END,
    Target.operating_system = Source.operating_system,
    Target.application_normalized_name = Source.[ci_installed_application normalized_name]
WHEN NOT MATCHED BY Source THEN
    DELETE;

2. 可选：用CTE简化重复逻辑 #

如果不想重复编写CASE语句，可以用CTE先预处理Source表的computer_name：

DELETE FROM clean_tanium_server;
WITH ProcessedSource AS (
    SELECT 
        computer_id,
        CASE 
            WHEN CHARINDEX('.', computer_name) > 0 
            THEN LEFT(computer_name, CHARINDEX('.', computer_name) - 1)
            ELSE computer_name
        END AS computer_name,
        operating_system,
        [ci_installed_application name] AS application_name,
        [ci_installed_application normalized_name] AS application_normalized_name,
        chassis_type,
        cpu_core,
        cpu_processor,
        ip_address
    FROM tanium_server
)
MERGE clean_tanium_server AS Target
USING ProcessedSource AS Source
ON Source.computer_id = Target.computer_id AND Source.application_name = Target.application_name
WHEN NOT MATCHED BY Target THEN
    INSERT (computer_id, computer_name, operating_system, application_name, application_normalized_name, chassis_type, cpu_core, cpu_processor, ip_address)
    VALUES (Source.computer_id, Source.computer_name, Source.operating_system, Source.application_name, Source.application_normalized_name, Source.chassis_type, Source.cpu_core, Source.cpu_processor, Source.ip_address)
WHEN MATCHED THEN UPDATE SET
    Target.computer_name = Source.computer_name,
    Target.operating_system = Source.operating_system,
    Target.application_normalized_name = Source.application_normalized_name
WHEN NOT MATCHED BY Source THEN
    DELETE;

验证效果 #

执行修正后的代码后，clean_tanium_server表的computer_name列会统一显示为点号前的部分，不带后缀；同时兼容无点号的服务器名称，不会触发报错。

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