CODEWITHSUNDEEP
sql-servert-sqlprimary-key
klaydze
klaydze

Reputation: 981

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 ('[email protected]', 1)
INSERT @tvpEmailType VALUES ('[email protected]', 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);

Upvotes: 0

Views: 1526

Answers (1)

paparazzo
paparazzo

Reputation: 45096

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 ('[email protected]', 1), ('[email protected]', 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

Upvotes: 1

Related Questions