SQL Server – Resolving Merge Problems with Unique Constraints

mergesql serverunique-constraint

I'm trying to create a procedure that will take the values from a temporary table, and load them into another table. The second table has a composite unique constraint across its three columns (Not including the primary key) I'm using a merge statement to try and handle the upload, and keep getting an error saying
Msg 2627, Level 14, State 1, Line 13

Violation of UNIQUE KEY constraint 'IX_table'. 
Cannot insert duplicate key in object 'dbo.table'. The duplicate key value is (aaa, aaa, aaa).

I'm really confused because the whole reason I am using the merge statement is to prevent exactly this from happening. Here's the relevant portion of the procedure (With names changed, and the insertion to the temporary table changed)

create table #TempTable(Id int
                        , str1 varchar(3)
                        , str2 varchar(15)
                        , str3 varchar(10))

INSERT INTO #TempTable
           (str1 
           ,str2 
           ,str3)
     VALUES
           ('aaa','aaa','aaa'), ('bbb', 'bbb', 'bbb'), ('aaa','aaa','aaa')

MERGE dbo.table AS t
    USING #TempTable AS s
    ON (t.str1 = s.str1
    AND t.str2 = s.str2
    AND t.str3 = s.str3)
    WHEN MATCHED THEN 
        UPDATE SET t.str1 = s.str1
    WHEN NOT MATCHED THEN
        INSERT (str1, str2, str3)
        VALUES (s.str1, s.str2, s.str3);

drop table #TempTable

I'm really confused as to why this isn't detecting duplicates, any ideas?

Thanks in advance

Best Answer

You will get that any time your input file has duplicates. In theory the MERGE command is doing everything in a batch so if you have the same row in the input data more than once it will try to insert it into the output table more than once giving you the error. Either dedupe your input file or use a CTE at the beginning of the MERGE to take care of it for you.

create table #TempTable(Id int
                        , str1 varchar(3)
                        , str2 varchar(15)
                        , str3 varchar(10))

INSERT INTO #TempTable
           (str1 
           ,str2 
           ,str3)
     VALUES
           ('aaa','aaa','aaa'), ('bbb', 'bbb', 'bbb'), ('aaa','aaa','aaa');


WITH dedupe AS (SELECT DISTINCT str1, str2, str3 FROM #TempTable)
MERGE dbo.[table] AS t
    USING dedupe AS s
    ON (t.str1 = s.str1
    AND t.str2 = s.str2
    AND t.str3 = s.str3)
    WHEN MATCHED THEN 
        UPDATE SET t.str1 = s.str1
    WHEN NOT MATCHED THEN
        INSERT (str1, str2, str3)
        VALUES (s.str1, s.str2, s.str3);

drop table #TempTable
Related Question