如何创建SQL Server存储过程：跳过已存在数据仅插入新数据

我来帮你搞定这个问题！你需要实现的是仅插入目标表中不存在的记录，避免重复数据。核心思路是在插入前先检查Prezente表里是否已经存在符合条件的记录，下面给你几种SQL Server中常用且靠谱的实现方式：

方法1：使用NOT EXISTS（推荐，可读性强） #

这是最常用的方式，逻辑清晰，性能也不错。我们需要先确定判断重复的唯一字段组合（比如员工ID、日期、工时类型，这三个组合应该是唯一的，避免同一天同一员工同一工时类型重复插入），然后在SELECT语句后添加NOT EXISTS子句过滤已存在的记录。

修改后的存储过程代码如下：

ALTER PROCEDURE [dbo].[PrezentaImport2] 
    @CodAngajat INT, 
    @Data DATE, 
    @CodTipOra VARCHAR(50), 
    @R1DAL NUMERIC(10,1), 
    @R1ALL NUMERIC(10,1), 
    @R1TOT NUMERIC(10,1), 
    @IdUtilizatorAdaugare INT 
AS
INSERT INTO Prezente (
    IdAngajat, 
    IdTipPostDeLucru, 
    IdPostDeLucru, 
    IdDepartament, 
    IdEchipa, 
    IdLinie, 
    Data, 
    IdTipOra, 
    R1DAL, 
    R1ALL, 
    R1TOT, 
    IdUtilizatorAdaugare, 
    DataAdaugare
)
SELECT 
    Angajati.Id, 
    Angajati.IdTipPostDeLucru, 
    Angajati.IdPostDeLucru, 
    Angajati.IdDepartament, 
    Angajati.IdEchipa, 
    Angajati.IdLinie, 
    @Data, 
    TipuriOre.Id, 
    @R1DAL, 
    @R1ALL, 
    @R1TOT, 
    @IdUtilizatorAdaugare, 
    GETDATE()
FROM Angajati 
INNER JOIN TipuriOre ON TipuriOre.CodTipOra = @CodTipOra 
WHERE Angajati.CodAngajat = @CodAngajat
-- 关键：过滤已存在的记录
AND NOT EXISTS (
    SELECT 1 FROM Prezente p
    WHERE p.IdAngajat = Angajati.Id
      AND p.Data = @Data
      AND p.IdTipOra = TipuriOre.Id
)

方法2：使用MERGE语句（适合需同时处理插入/更新的场景） #

如果以后你的需求扩展为“存在则更新，不存在则插入”，MERGE语句会更合适。即使现在只需要插入，也可以用它来实现：

ALTER PROCEDURE [dbo].[PrezentaImport2] 
    @CodAngajat INT, 
    @Data DATE, 
    @CodTipOra VARCHAR(50), 
    @R1DAL NUMERIC(10,1), 
    @R1ALL NUMERIC(10,1), 
    @R1TOT NUMERIC(10,1), 
    @IdUtilizatorAdaugare INT 
AS
MERGE INTO Prezente AS Target
USING (
    SELECT 
        Angajati.Id, 
        Angajati.IdTipPostDeLucru, 
        Angajati.IdPostDeLucru, 
        Angajati.IdDepartament, 
        Angajati.IdEchipa, 
        Angajati.IdLinie, 
        @Data AS Data, 
        TipuriOre.Id AS IdTipOra, 
        @R1DAL, 
        @R1ALL, 
        @R1TOT, 
        @IdUtilizatorAdaugare, 
        GETDATE() AS DataAdaugare
    FROM Angajati 
    INNER JOIN TipuriOre ON TipuriOre.CodTipOra = @CodTipOra 
    WHERE Angajati.CodAngajat = @CodAngajat
) AS Source
ON Target.IdAngajat = Source.Id
   AND Target.Data = Source.Data
   AND Target.IdTipOra = Source.IdTipOra
-- 当目标表中无匹配记录时插入
WHEN NOT MATCHED THEN
INSERT (
    IdAngajat, 
    IdTipPostDeLucru, 
    IdPostDeLucru, 
    IdDepartament, 
    IdEchipa, 
    IdLinie, 
    Data, 
    IdTipOra, 
    R1DAL, 
    R1ALL, 
    R1TOT, 
    IdUtilizatorAdaugare, 
    DataAdaugare
)
VALUES (
    Source.Id, 
    Source.IdTipPostDeLucru, 
    Source.IdPostDeLucru, 
    Source.IdDepartament, 
    Source.IdEchipa, 
    Source.IdLinie, 
    Source.Data, 
    Source.IdTipOra, 
    Source.R1DAL, 
    Source.R1ALL, 
    Source.R1TOT, 
    Source.IdUtilizatorAdaugare, 
    Source.DataAdaugare
);

方法3：使用LEFT JOIN + WHERE IS NULL #

和NOT EXISTS逻辑类似，通过左连接判断目标表中无匹配记录：

ALTER PROCEDURE [dbo].[PrezentaImport2] 
    @CodAngajat INT, 
    @Data DATE, 
    @CodTipOra VARCHAR(50), 
    @R1DAL NUMERIC(10,1), 
    @R1ALL NUMERIC(10,1), 
    @R1TOT NUMERIC(10,1), 
    @IdUtilizatorAdaugare INT 
AS
INSERT INTO Prezente (
    IdAngajat, 
    IdTipPostDeLucru, 
    IdPostDeLucru, 
    IdDepartament, 
    IdEchipa, 
    IdLinie, 
    Data, 
    IdTipOra, 
    R1DAL, 
    R1ALL, 
    R1TOT, 
    IdUtilizatorAdaugare, 
    DataAdaugare
)
SELECT 
    Angajati.Id, 
    Angajati.IdTipPostDeLucru, 
    Angajati.IdPostDeLucru, 
    Angajati.IdDepartament, 
    Angajati.IdEchipa, 
    Angajati.IdLinie, 
    @Data, 
    TipuriOre.Id, 
    @R1DAL, 
    @R1ALL, 
    @R1TOT, 
    @IdUtilizatorAdaugare, 
    GETDATE()
FROM Angajati 
INNER JOIN TipuriOre ON TipuriOre.CodTipOra = @CodTipOra 
LEFT JOIN Prezente p 
    ON p.IdAngajat = Angajati.Id
    AND p.Data = @Data
    AND p.IdTipOra = TipuriOre.Id
WHERE Angajati.CodAngajat = @CodAngajat
  AND p.IdAngajat IS NULL -- 目标表中无匹配记录

额外建议 #

为了从根源上避免重复数据，建议在Prezente表上创建唯一约束或唯一索引，比如：

CREATE UNIQUE NONCLUSTERED INDEX IX_Prezente_Angajat_Data_TipOra
ON Prezente (IdAngajat, Data, IdTipOra);

这样即使存储过程的逻辑有疏漏，数据库也会直接阻止重复数据插入，同时还能提升查询判断重复的性能。

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