SQL Server存储过程Insert if not exists新增记录不生效问题求助

• 现有存储过程采用全局存在性判断逻辑：只要临时表@jsontable中有任意一条记录已存在于目标consultation表，IF NOT EXISTS判断就会返回假，直接跳过整个插入逻辑块，新增记录自然无法写入。
• 你当前的判断是作用于全量数据集，而非逐条判断每条记录的插入/更新需求，这是问题的核心诱因。
  额外潜在bug：你将入参@consultation_json（NVARCHAR类型）赋值给@JSON（VARCHAR类型），可能会丢失中文等Unicode字符，建议统一用NVARCHAR类型

推荐使用SQL Server原生MERGE语句实现逐条Upsert（更新+插入）逻辑，替换原有全局判断的写法，修改后的完整存储过程如下：

ALTER PROCEDURE [dbo].[load_consultation_data] 
    @consultation_json NVARCHAR(MAX)
AS  
BEGIN
    SET NOCOUNT ON;
    -- 修复类型转换问题，避免丢失Unicode字符
    DECLARE @JSON NVARCHAR(MAX) = @consultation_json
    
    DECLARE @jsontable table 
    (
        customerID NVARCHAR(50),
        clinicID NVARCHAR(50),
        birdId NVARCHAR(80),                 
        consultationId NVARCHAR(80),  
        employeeName NVARCHAR(150),
        totalPrice MONEY,               
        row_num int
    ) 

    /* 导入嵌套JSON数据 */
    INSERT INTO @jsontable  
    SELECT 
        customer.customerID AS customerID
        ,customer.clinicID AS clinicID 
        ,consultation.birdId AS birdId 
        ,consultation.consultationId AS consultationId         
        ,consultation.employeeName AS employeeName            
        ,consultation.totalPrice AS totalPrice               
        ,ROW_NUMBER() OVER (ORDER BY consultationId ) row_num            
    FROM OPENJSON (@JSON, '$')
    WITH (
        customerID VARCHAR(50) '$.customerID',
        clinicID VARCHAR(50) '$.clinic.clinicID'
    ) as customer
    CROSS APPLY openjson(@json,'$.clinic.consultation')
    WITH(
        birdId NVARCHAR(80),
        consultationId NVARCHAR(80),                                   
        employeeName NVARCHAR(150),     
        totalPrice MONEY           
    ) as consultation       
    INNER JOIN dbo.clinic AS clinic_tab ON clinic_tab.external_id = customer.clinicID
    INNER JOIN dbo.bird AS bird_tab ON bird_tab.external_bird_id = consultation.birdId

    /* 用MERGE实现逐条Upsert */
    MERGE dbo.consultation AS target
    USING (
        SELECT   
            rjson.consultationId AS external_consultation_id   
            ,rjson.[birdId] AS external_bird_id         
            ,bird_tab.clinic_id AS clinic_id                    
            ,bird_tab.[id] AS bird_id    
            ,rjson.employeeName AS employee_name 
            ,rjson.totalPrice AS total_price   
        FROM @jsontable as rjson                        
        INNER JOIN dbo.bird AS bird_tab on bird_tab.external_bird_id = rjson.birdId 
        INNER JOIN dbo.clinic AS clinic_tab on clinic_tab.external_id = rjson.clinicID 
        WHERE bird_tab.clinic_id = clinic_tab.id 
    ) AS source
    -- 匹配规则和你原有校验逻辑一致
    ON (
        target.external_consultation_id = source.external_consultation_id
        AND target.clinic_id = source.clinic_id
        AND target.external_bird_id = source.external_bird_id
    )
    -- 匹配到的记录执行更新
    WHEN MATCHED THEN
        UPDATE SET 
            external_bird_id = source.external_bird_id,
            clinic_id = source.clinic_id,
            bird_id = source.bird_id,
            employee_name = source.employee_name,
            total_price = source.total_price
    -- 未匹配到的记录执行插入
    WHEN NOT MATCHED THEN
        INSERT (
            external_consultation_id,
            external_bird_id,
            clinic_id,
            bird_id,
            employee_name,
            total_price
        )
        VALUES (
            source.external_consultation_id,
            source.external_bird_id,
            source.clinic_id,
            source.bird_id,
            source.employee_name,
            source.total_price
        );
END

• 基于MERGE的逻辑会对临时表中每一条记录单独做匹配校验，已存在的记录执行更新，不存在的新记录自动执行插入，完全符合你追加JSON记录后增量同步的需求
• 保留了原有所有的表关联校验逻辑，不会引入业务规则层面的问题
• 修复了原有代码中NVARCHAR转VARCHAR可能导致的Unicode字符丢失问题

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