如何避免向Process表重复插入完全相同的记录？

你的问题核心是当前插入逻辑只校验了Source是否存在于Hold_Table，但没有检查Process表中是否已经存在完全相同的记录，导致重复执行时会重复插入。要实现当Hold、GId、Source、Type、Operation（固定为'ADD'）全部相同时不重复插入，否则允许插入的需求，有两种可靠的处理方式：

方式一：在插入逻辑中增加重复校验 #

直接修改INSERT语句，在WHERE条件里新增对Process表的检查，确保待插入的记录在Process中不存在完全匹配的条目：

INSERT INTO Process (Hold, GId, Source, Type, Operation)
SELECT Hold, GID, Source, Type, 'ADD' AS Operation 
FROM Temp_Table Thi
WHERE NOT EXISTS (
    SELECT 1
    FROM Hold_Table Hi 
    WHERE Thi.Source = Hi.Source
)
AND Thi.Source <> 'NOT FOUND'
AND LEN(TRIM(ISNULL(Thi.Source, ''))) > 0
-- 新增：检查Process中是否已存在相同的Hold/GId/Source/Type且Operation为ADD的记录
AND NOT EXISTS (
    SELECT 1
    FROM Process P
    WHERE P.Hold = Thi.Hold
      AND P.GId = Thi.GId
      AND P.Source = Thi.Source
      AND P.Type = Thi.Type
      AND P.Operation = 'ADD'
);

注意事项 #

如果Hold、GId、Type这些列可能存在NULL值，上面的等值判断会失效（因为NULL = NULL在SQL中返回未知），需要调整匹配逻辑：

-- 处理NULL值的匹配条件示例
AND NOT EXISTS (
    SELECT 1
    FROM Process P
    WHERE (P.Hold = Thi.Hold OR (P.Hold IS NULL AND Thi.Hold IS NULL))
      AND (P.GId = Thi.GId OR (P.GId IS NULL AND Thi.GId IS NULL))
      AND (P.Source = Thi.Source OR (P.Source IS NULL AND Thi.Source IS NULL))
      AND (P.Type = Thi.Type OR (P.Type IS NULL AND Thi.Type IS NULL))
      AND P.Operation = 'ADD'
);

如果你的数据库支持IS NOT DISTINCT FROM（比如SQL Server 2022+、PostgreSQL），可以简化为：

AND NOT EXISTS (
    SELECT 1
    FROM Process P
    WHERE P.Hold IS NOT DISTINCT FROM Thi.Hold
      AND P.GId IS NOT DISTINCT FROM Thi.GId
      AND P.Source IS NOT DISTINCT FROM Thi.Source
      AND P.Type IS NOT DISTINCT FROM Thi.Type
      AND P.Operation = 'ADD'
);

方式二：给Process表添加唯一约束 #

从数据库层面强制约束，避免任何途径的重复插入，这是更可靠的方案：

ALTER TABLE Process
ADD CONSTRAINT UQ_Process_Unique_Add_Record
UNIQUE (Hold, GId, Source, Type, Operation);

添加约束后，当尝试插入重复记录时，数据库会直接抛出错误，阻止插入。如果需要避免报错，可以结合MERGE（SQL Server、Oracle）语法，让重复时不执行操作：

SQL Server示例（MERGE语法） #

MERGE INTO Process AS Target
USING (
    SELECT Hold, GID, Source, Type, 'ADD' AS Operation 
    FROM Temp_Table Thi
    WHERE NOT EXISTS (
        SELECT 1
        FROM Hold_Table Hi 
        WHERE Thi.Source = Hi.Source
    )
    AND Thi.Source <> 'NOT FOUND'
    AND LEN(TRIM(ISNULL(Thi.Source, ''))) > 0
) AS Source
ON Target.Hold = Source.Hold
   AND Target.GId = Source.GId
   AND Target.Source = Source.Source
   AND Target.Type = Source.Type
   AND Target.Operation = Source.Operation
WHEN NOT MATCHED THEN
    INSERT (Hold, GId, Source, Type, Operation)
    VALUES (Source.Hold, Source.GId, Source.Source, Source.Type, Source.Operation);

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