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