Merge Problems SQL Server -

Merge Problems SQL Server -

i'm trying create procedure take values temporary table, , load them table. sec table has composite unique constraint across 3 columns (not including primary key) i'm using merge statement seek , handle upload, , maintain getting 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'. duplicate key value (aaa, aaa, aaa).

i'm confused because whole reason using merge statement prevent happening. here's relevant portion of procedure (with names changed, , insertion temporary table changed)

create table #temptable(id int , str1 varchar(3) , str2 varchar(15) , str3 varchar(10)) insert #temptable (str1 ,str2 ,str3) values ('aaa','aaa','aaa'), ('bbb', 'bbb', 'bbb'), ('aaa','aaa','aaa') merge dbo.table t using #temptable s on (t.str1 = s.str1 , t.str2 = s.str2 , t.str3 = s.str3) when matched update set t.str1 = s.str1 when not matched insert (str1, str2, str3) values (s.str1, s.str2, s.str3); drop table #temptable

i'm confused why isn't detecting duplicates, ideas?

thanks in advance

you updating same row multiple times in same statement. understand dml set-based. rows not applied 1 after other. forcefulness inefficient execution plans , not in spirit of sql.

i'm not sure how duplicate updates defined in sql server. believe sql server defines at to the lowest degree one of duplicate updates happen. unclear 1 comes through.

here, dealing inserts. you're inserting same row twice. writes computed before write executed. that's why 2 inserts beingness "queued".

sql-server merge unique-key