如何在合并时避免从源表中插入重复记录？

Question

我遇到一个情景，需要下载并更新一个包含代码及描述的目录。因此，我尝试将这些数据导入到临时表中，并使用合并语句进行插入/更新到我的目标表中。但是我遇到了一个问题，即我的临时表中有重复记录，而我使用的合并语句在目标表中插入了重复记录。

示例：临时表中有数据

   Code   Description
    C1       Desc1
    C2       Desc2
    C1       Desc1    

我希望您能将目标表数据翻译为以下内容：

   Code   Description
    C1       Desc1
    C2       Desc2

我使用了以下合并语句：

 MERGE INTO Frames as Target using Temp as Source
 on Source.Code=Target.Code
 WHEN MATCHED THEN  UPDATE  set Target.Description=Source.Description 
 WHEN NOT MATCHED THEN insert (Code,Description) values (Code,Description);

但最终我的目标表格有数据。

   Code   Description
    C1       Desc1
    C2       Desc2
    C1       Desc1   

我知道这对于你们中的一些人来说可能是一个简单的查询。但是我不擅长 Sql，所以期望得到我的问题的解决方案。

提前致谢。

- user2502961

---

可能是https://dev59.com/1Ww15IYBdhLWcg3wd7lj?rq=1的重复问题。 - user704988

你的表格不应该允许重复。代码上应该有一个唯一索引。 - Tim

Answer 1

只需不直接使用Temp作为源，而是首先对其进行筛选以获取不同的值

MERGE INTO Frames as Target 
using (SELECT DISTINCT * FROM Temp) as Source
on Source.Code=Target.Code
WHEN MATCHED THEN  UPDATE  set Target.Description=Source.Description 
WHEN NOT MATCHED THEN insert (Code,Description) values (Code,Description);

- Nenad Zivkovic

Answer 2

在目标表上创建唯一索引以防止重复。

- Randy

---

我投了你一票。当我在上面发表评论时，屏幕底部没有看到你的答案。 - Tim

Answer 3

如果你在一个不支持MERGE语句的数据库中，避免从源表插入重复记录的另一种方法是只使用UPDATE和INSERT语句：

CREATE PROC [DW].[Load_Quest_Staff_Curricula_D] AS

---------- When Not Matched By Source ----------

INSERT INTO DW.Quest_Staff_Curricula_D 
(
    User_Id,
    Full_Name,
    Job_Type_Name,
    First_Name,
    Middle_Name,
    Last_Name,
    Office_Name,
    Office_Num,
    Start_Date,
    Date_Terminated,
    Curriculum_Name,
    Curriculum_Status,
    IsCurrent,
    DateCreated,
    DateModified
)

SELECT
s.Person_AD_Name AS User_Id,
s.Person_Full_Name AS Full_Name,
s.Person_Job_Type_Name AS Job_Type_Name,
s.Person_First_Name AS First_Name,
s.Person_Middle_Name AS Middle_Name, 
s.Person_Last_Name AS Last_Name,
s.Person_Office_Name AS Office_Name,
s.Person_Office_Num AS Office_Num,
s.Person_Start_Date AS Start_Date,
s.Person_Date_Terminated AS Date_Terminated,
s.Curriculum_Name AS Curriculum_Name,
s.Student_Curriculum_Status AS Curriculum_Status,
1 AS IsCurrent,
GETDATE() AS DateCreated,
GETDATE() AS DateModified
FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY Person_AD_Name ORDER BY Person_AD_Name) AS Row
    FROM SABA.SABA_Curricula 
) as s 
LEFT JOIN DW.Quest_Staff_Curricula_D T
ON T.User_Id = s.Person_AD_Name
WHERE T.User_Id is NULL
AND row = 1

---------- When Matched -----------

UPDATE T
SET T.User_Id = s.Person_AD_Name,
T.Full_Name = s.Person_Full_Name,
T.Job_Type_Name = s.Person_Job_Type_Name,
T.First_Name = s.Person_First_Name,
T.Middle_Name = s.Person_Middle_Name,
T.Last_Name = s.Person_Last_Name,
T.Office_Name = s.Person_Office_Name,
T.Office_Num = s.Person_Office_Num,
T.Start_Date = s.Person_Start_Date,
T.Date_Terminated = s.Person_Date_Terminated,
T.Curriculum_Name = s.Curriculum_Name,
T.Curriculum_Status = s.Student_Curriculum_Status,
T.DateModified = GETDATE()
FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY Person_AD_Name ORDER BY Person_AD_Name) AS Row
    FROM SABA.SABA_Curricula 
) as s 
LEFT JOIN DW.Quest_Staff_Curricula_D T
ON T.User_Id = s.Person_AD_Name
WHERE T.User_ID IS NOT NULL
AND row = 1

---------- When Matched -----------

UPDATE DW.Quest_Staff_Curricula_D
SET IsCurrent = 0
FROM DW.Quest_Staff_Curricula_D T
LEFT JOIN (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY Person_AD_Name ORDER BY Person_AD_Name) AS Row
    FROM SABA.SABA_Curricula 
) AS s 
ON T.User_Id = s.Person_AD_Name
WHERE row = 1
AND T.User_ID IS NULL 
AND isCurrent = 1

希望这能有所帮助。

- JTD2021