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 record was inserted in the target table and if there is another same primary key that will be inserted, it should be execute an UPDATE not INSERT to avoid the primary key violation, but I don't know to write it in actual sql script. As of know, I only have the below script.

// User-Defined Tabled Type
DECLARE @tvpEmailType dbo.EmailType

INSERT @tvpEmailType VALUES ('[e-mail]', 1)
INSERT @tvpEmailType VALUES ('[e-mail]', 0)

MERGE dbo.EmailRepo AS TARGET
USING (SELECT DISTINCT * FROM @tvpEmailType) AS SOURCE
    ON (TARGET.Email = SOURCE.Email)
WHEN MATCHED AND TARGET.Status <> SOURCE.Status THEN
    UPDATE SET TARGET.Status = SOURCE.Status
WHEN NOT MATCHED THEN
    INSERT (Email, Status) VALUES (SOURCE.Email, SOURCE.Status);

回答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 ('[e-mail]', 1), ('[e-mail]', 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