Merge Problems SQL Server

Question

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

• Aaron Bertrand: As an aside, please read this. MERGE sounds great until one of these issues bites you. Hard.

Answer 1

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