Inserting records into SQL Server, how to update on conflict?

q35jwt9p  于 2023-03-28  发布在  SQL Server
关注(0)|答案(2)|浏览(151)

I have a statement that looks like this:

Insert into table (value1, value2, value3)
Values (%s, %s, %s)

Could I do something where it will attempt to insert, but on conflict update value2 and value3 where value1 = value1 (i.e., value1 equals the duplicate primary key that is trying to be inserted against)?

4smxwvx5

4smxwvx51#

For one insert (assuming value1 is a key)

UPDATE TOP (1) table
     SET value2 = %s, value3 = %s
   WHERE value1 = %s;
 
  IF (@@ROWCOUNT = 0)
  BEGIN      
      INSERT table (value1, value2, value3)
      VALUES (%s, %s, %s);
  END

For multiple inserts

  1. define a procedure for a table
CREATE PROCEDURE table_upsert  (@value1 bigint, @value2 nvarchar(255), @value3 nvarchar(255))
AS 
BEGIN TRAN
 
  UPDATE TOP (1) table WITH (UPDLOCK, SERIALIZABLE)
     SET value2 = @value2, value3 = @value3
   WHERE value1 = @value1;
 
  IF (@@ROWCOUNT = 0)
  BEGIN      
      INSERT table (value1, value2, value3)
      VALUES (@value1, @value2, @value3);
  END 
COMMIT
  1. call the procedure with parameters
EXEC table_upsert %s, %s, %s
hyrbngr7

hyrbngr72#

INSERT INTO table (value1, value2, value3) VALUES(%s, %s, %s) 
ON DUPLICATE KEY 
UPDATE  value2=%s, value3=%s

相关问题