CODEWITHSUNDEEP
sqlsql-server
user2502961
user2502961

Reputation: 157

How to avoid inserting duplicate records from source table while merge?

I'm into a scenario where I need to download and update a catalog which has codes and its descriptions in it. So I'm trying to import this data into a temp table and do an insert / update to my target table using merge statement but I ended up with an issue where my temp table had duplicate records and the merge statement which I was used inserted duplicate records into the target table.

Example: Temp table has data 

   Code   Description
    C1       Desc1
    C2       Desc2
    C1       Desc1

and I want my target table data as

   Code   Description
    C1       Desc1
    C2       Desc2

I used below merge statement:

 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);

but finally my target table has data 

   Code   Description
    C1       Desc1
    C2       Desc2
    C1       Desc1

I know this may be a simple query to some of you. But I am not strong in Sql, so expecting some solution for my problem.

Thanks in advance.

Upvotes: 6

Views: 7847

Answers (3)

JTD2021
JTD2021

Reputation: 156

Another way to avoid inserting duplicate records from the source table while merging is to just use an UPDATE and INSERT statement if you're ever in a database that doesn't support the MERGE statement:

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

Hope this helps.

Upvotes: 0

Nenad Zivkovic
Nenad Zivkovic

Reputation: 18559

Just don't use Temp directily as Source but filter it for distinct values first

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);

Upvotes: 9

Randy
Randy

Reputation: 16677

create a unique index on the target table to prevent the duplicates.

Upvotes: 3

Related Questions