CODEWITHSUNDEEP
mysqlsql-servermergeupsert
Vincent L
Vincent L

Reputation: 739

UPSERT multiple records MSSQL

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.

Upvotes: 6

Views: 9857

Answers (3)

John Tamburo
John Tamburo

Reputation: 447

This is a perfect candidate for MERGE, as others have noted.

Here is an example. Sourcetable and Tablename have the same layout in this example, but as long as you know the match column name and the data column names, this will work.

MERGE TableName AS TARGET
USING SourceTable AS SOURCE 
ON (TARGET.Keycol = SOURCE.Keycol) 
WHEN MATCHED 
AND 
(
    TARGET.Col1 != SOURCE.Col1 OR TARGET.Col2 != SOURCE.Col2
) 
THEN 
UPDATE 
SET 
    TARGET.Col1 = SOURCE.Col1
    , TARGET.col2 = SOURCE.Col2 
WHEN NOT MATCHED BY TARGET 
THEN INSERT (keycol,col1,col2) VALUES (SOURCE.KeyCol, SOURCE.Col1, SOURCE.Col2)
;

This is all you need to do. It is easily adaptable to dynamic tables and even temp tables. Table VARIABLES are tricky but everything else is easily accomplished.

Upvotes: 2

Lukasz Szozda
Lukasz Szozda

Reputation: 175994

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

Upvotes: 16

Nielsen Martins Gon&#231;alves
Nielsen Martins Gon&#231;alves

Reputation: 372

Take a look at this.

Equivalent of MySQL ON DUPLICATE KEY UPDATE in Sql Server

I think it will solve your problem.

Upvotes: 1

Related Questions