如何在存储过程中向[Cop].[fact_capacidadOperativa]插入不存在的行？

你的NOT EXISTS方案本身是可靠且高效的，尤其是在SQL Server环境下，查询优化器对这类逻辑的处理已经很成熟，但是否为最优方案还要结合你的数据量、索引情况和业务场景来判断，下面分情况给你拆解：

一、先确认你的NOT EXISTS写法是否正确（示例参考） #

假设你的唯一判断键是id_col1和id_col2（复合键），标准写法应该是这样：

INSERT INTO [Cop].[fact_capacidadOperativa] (col1, col2, col3, ...)
SELECT src.col1, src.col2, src.col3, ...
FROM (
    -- 你的多表关联查询逻辑
    SELECT t1.col1, t2.col2, t3.col3, ...
    FROM table1 t1
    JOIN table2 t2 ON t1.id = t2.t1_id
    JOIN table3 t3 ON t2.id = t3.t2_id
) AS src
WHERE NOT EXISTS (
    SELECT 1
    FROM [Cop].[fact_capacidadOperativa] tgt
    WHERE tgt.id_col1 = src.id_col1
      AND tgt.id_col2 = src.id_col2 -- 这里是判断行是否存在的唯一标识列
)

这个写法的优势是逻辑直观，查询优化器能很好地利用目标表的索引进行存在性检查，避免全表扫描。

二、不同场景下的替代方案对比 #

1. MERGE语句（适合需同时处理插入/更新的场景） #

如果你的业务后续可能需要对已存在的行做更新，MERGE可以一步完成，但单纯做插入的话，它的性能不会比NOT EXISTS更好，甚至可能因为逻辑复杂导致优化器选择较差的执行计划：

MERGE INTO [Cop].[fact_capacidadOperativa] tgt
USING (
    -- 你的多表关联查询
    SELECT t1.col1, t2.col2, t3.col3, ..., t1.id_col1, t2.id_col2
    FROM table1 t1
    JOIN table2 t2 ON t1.id = t2.t1_id
    JOIN table3 t3 ON t2.id = t3.t2_id
) AS src
ON tgt.id_col1 = src.id_col1 AND tgt.id_col2 = src.id_col2
WHEN NOT MATCHED THEN
    INSERT (col1, col2, col3, ...)
    VALUES (src.col1, src.col2, src.col3, ...);

注意：MERGE在高并发场景下可能会有死锁风险，需要额外注意隔离级别或锁提示。

2. LEFT JOIN + IS NULL（旧版本SQL Server可选） #

这个写法逻辑和NOT EXISTS类似，但在SQL Server 2008及以前的版本中，优化器可能不会生成和NOT EXISTS一样高效的执行计划，现在新版本已经差异不大，但可读性稍差：

INSERT INTO [Cop].[fact_capacidadOperativa] (col1, col2, col3, ...)
SELECT src.col1, src.col2, src.col3, ...
FROM (
    -- 你的多表关联查询
    SELECT t1.col1, t2.col2, t3.col3, ..., t1.id_col1, t2.id_col2
    FROM table1 t1
    JOIN table2 t2 ON t1.id = t2.t1_id
    JOIN table3 t3 ON t2.id = t3.t2_id
) AS src
LEFT JOIN [Cop].[fact_capacidadOperativa] tgt
    ON tgt.id_col1 = src.id_col1 AND tgt.id_col2 = src.id_col2
WHERE tgt.id_col1 IS NULL;

3. EXCEPT（适合列集合匹配的场景） #

如果源查询的输出列和目标表用来判断唯一性的列完全匹配，EXCEPT可以写出更简洁的代码，它的底层逻辑和NOT EXISTS类似，但要注意NULL值的处理（EXCEPT会将NULL视为相等，符合SQL标准）：

INSERT INTO [Cop].[fact_capacidadOperativa] (id_col1, id_col2, col3, ...)
SELECT id_col1, id_col2, col3, ...
FROM (
    -- 你的多表关联查询
    SELECT t1.id_col1, t2.id_col2, t3.col3, ...
    FROM table1 t1
    JOIN table2 t2 ON t1.id = t2.t1_id
    JOIN table3 t3 ON t2.id = t3.t2_id
) AS src
EXCEPT
SELECT id_col1, id_col2, col3, ...
FROM [Cop].[fact_capacidadOperativa];

三、性能优化的核心要点 #

不管用哪种方案，以下几点能直接影响执行效率：

• 给目标表的唯一判断列加索引：比如给id_col1和id_col2创建复合唯一索引，这会让存在性检查从全表扫描变成索引查找，性能提升巨大。
• 优化源查询的多表关联：确保关联列（比如t1.id、t2.t1_id）有索引，避免源查询本身成为性能瓶颈。
• 批量插入分批次：如果源数据量极大（比如百万级以上），不要一次性插入，分批次执行可以减少锁竞争和日志压力。
• 并发场景加锁提示：如果有其他进程同时写入目标表，在NOT EXISTS的子查询中加上UPDLOCK, HOLDLOCK提示，可以避免幻读和并发冲突：

  WHERE NOT EXISTS (
      SELECT 1
      FROM [Cop].[fact_capacidadOperativa] tgt WITH (UPDLOCK, HOLDLOCK)
      WHERE tgt.id_col1 = src.id_col1
        AND tgt.id_col2 = src.id_col2
  )
  

总结：如果只是单纯插入不存在的行，你的NOT EXISTS方案就是最优选择之一，只要配合合适的索引，性能完全没问题；如果有后续更新需求，可以考虑MERGE；列集合匹配的话用EXCEPT更简洁。

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