MERGE SQL Server 主键冲突 [英] MERGE SQL Server Primary Key Violation

问题描述

有没有可能成功执行下面的sql语句?目前，我在下面的查询中收到主键违规.

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

我想要的是，当第一条记录插入目标表时，如果有另一个相同的主键将被插入，则应该执行 UPDATE 而不是 INSERT 以避免主键冲突，但我不这样做不知道在实际的 sql 脚本中编写它.据了解，我只有以下脚本.

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);

推荐答案

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

这篇关于MERGE SQL Server 主键冲突的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！