UPSERT multiple records MSSQL

Question

I currently have a MySQL server, but I am currently migrating to MS SQL Server.

I am trying to insert a lot (2000) or records per batch in a table in a C# application. I want to replicate MySQL's "REPLACE INTO" (UPSERT), where if the record exists, I want to UPDATE it and if it doesn't, I want to INSERT it. I currently have a statement like this:

REPLACE INTO tablename (keycol, col1, col2)
VALUES ('A','B','C'),
       ('C','D','E'),
       ('F','G','H'),
       ('I','J','K');

Some of the records will exist and others won't. Also, this statement will run on many different tables and the values are built at runtime (by looping over each field dynamically), so the structure of the table is not known at compile time.

I doubt I can efficiently use MERGE because of the fact that some of the records in the batch will exist and some won't and that since I don't know the structure of the table before hand and the fact that I am updating batches of 2000 records, I don't want to use subqueries for performance reasons.

Any help on the best solution would be greatly appreciated.

Answer 1

MERGE was specifically designed for such scenarios:

CREATE TABLE tablename(keycol CHAR(1) PRIMARY KEY, col1 CHAR(1), col2 CHAR(1));
INSERT INTO tablename(keycol, col1, col2) VALUES('A', 'X', 'X'); -- to be updated
SELECT * FROM tablename;

MERGE tablename trg
USING (VALUES ('A','B','C'),
              ('C','D','E'),
              ('F','G','H'),
              ('I','J','K')) src(keycol, col1, col2)
  ON trg.keycol = src.keycol
WHEN MATCHED THEN
   UPDATE SET col1 = src.col1, col2 = src.col2
WHEN NOT MATCHED THEN
   INSERT(keycol, col1, col2)
   VALUES(src.keycol, src.col1, src.col2);

SELECT * FROM tablename;

db<>fiddle demo

Answer 2

Take a look at this.

Equivalent of MySQL ON DUPLICATE KEY UPDATE in Sql Server

I think it will solve your problem.