Merge Sql Server Primary Key Violation

is there any chance that I can execute the below sql statement successfully? Currently, I'm receiving Primary Key Violation on my query below. What I want is that, when the first

Solution 1:

Bingo

DECLARE @i table (iden int identity, email varchar(40), status bit);
DECLARE @t table (email varchar(40) primary key, status bit);

INSERT @i VALUES ('mail@mail.com', 1), ('mail@mail.com', 0)

MERGE @t AS TARGET
USING ( select email, status 
        from ( select email, status
                    , row_number() over (partition by email order by iden desc) as rn
                from @i
             ) t
             where t.rn = 1
      ) AS SOURCE
   ON TARGET.Email = SOURCE.Email
WHEN MATCHED THEN
    UPDATE SET TARGET.Status = SOURCE.Status
WHEN NOT MATCHED THEN
    INSERT (Email, Status) VALUES (SOURCE.Email, SOURCE.Status);

select * from @t