THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Davide Mauri

A place for my thoughts and experiences the Microsoft Data Platform

Insert Or update (aka Replace or Upsert)

The topic is really not new but since it’s the second time in few days that I had to explain it different customers, I think it’s worth to make a post out of it.

Many times developers would like to insert a new row in a table or, if the row already exists, update it with new data. MySQL has a specific statement for this action, called REPLACE:

http://dev.mysql.com/doc/refman/5.0/en/replace.html

or the INSERT …. ON DUPLICATE KEY UPDATE option:

http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

With SQL Server you can do the very same using a more standard way, using the MERGE statement, with the support of Row Constructors.

Let’s say that you have this table:

CREATE TABLE dbo.MyTargetTable
(
    id INT NOT NULL PRIMARY KEY IDENTITY,
    alternate_key VARCHAR(50) UNIQUE,
    col_1 INT,
    col_2 INT,
    col_3 INT,
    col_4 INT,
    col_5 INT
)
GO

INSERT [dbo].[MyTargetTable] VALUES
('GUQNH', 10, 100, 1000, 10000, 100000),
('UJAHL', 20, 200, 2000, 20000, 200000),
('YKXVW', 30, 300, 3000, 30000, 300000),
('SXMOJ', 40, 400, 4000, 40000, 400000),
('JTPGM', 50, 500, 5000, 50000, 500000),
('ZITKS', 60, 600, 6000, 60000, 600000),
('GGEYD', 70, 700, 7000, 70000, 700000),
('UFXMS', 80, 800, 8000, 80000, 800000),
('BNGGP', 90, 900, 9000, 90000, 900000),
('AMUKO', 100, 1000, 10000, 100000, 1000000)
GO

If you want to insert or update a row, you can just do that:

MERGE INTO
    dbo.MyTargetTable T
USING
    (SELECT * FROM (VALUES ('ZITKS', 61, 601, 6001, 60001, 600001)) Dummy(alternate_key, col_1, col_2, col_3, col_4, col_5)) S
ON
    T.alternate_key = S.alternate_key
WHEN
    NOT MATCHED THEN
    INSERT VALUES (alternate_key, col_1, col_2, col_3, col_4, col_5)
WHEN
    MATCHED AND T.col_1 != S.col_1 THEN
    UPDATE SET
        T.col_1 = S.col_1,
        T.col_2 = S.col_2,
        T.col_3 = S.col_3,
        T.col_4 = S.col_4,
        T.col_5 = S.col_5
;

If you want to insert/update more than one row at once, you can super-charge the idea using Table-Value Parameters, that you can just send from your .NET application.

Easy, powerful and effectiveSmile

Published Monday, October 29, 2012 8:29 PM by Davide Mauri

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Paul White said:

If concurrency is high, MERGE is not immune to problems - see http://weblogs.sqlteam.com/dang/archive/2009/01/31/UPSERT-Race-Condition-With-MERGE.aspx

October 31, 2012 5:57 PM
 

Kevin Boisits said:

Can make the Table Value Constructor a bit simpler:

USING

   (VALUES ('ZITKS', 61, 601, 6001, 60001, 600001)) S(alternate_key, col_1, col_2, col_3, col_4, col_5)

ON

You do not need the SELECT * and can name not columns on the S table.

November 2, 2012 11:12 AM
 

Davide Mauri said:

@Paul I haven't tested the specific situation the blog post descrives (will do ASAP) but I've tried hammering a MERGE statement with Adam's QueryStress and had no problem at all. Anyway, good to know!

November 4, 2012 6:16 PM
 

Davide Mauri said:

@Kevin: yeah, you're right. I'm just too much used to the "SELECT" statement :)

November 4, 2012 6:16 PM

Leave a Comment

(required) 
(required) 
Submit

About Davide Mauri

Director of Software Development & Cloud Infrastructure @ Sensoria, an innovative smart garments and wearable company. After more than 15 year playing with the Microsoft Data Platform, with a specific focus on High Performance databases, Business Intelligence, Data Science and Data Architectures, he's now applying all his skills to IoT, defining architectures to crunch numbers, create nice user experiences and provide meaningful insights, all leveraging Microsoft Azure cloud. MVP on Data Platform since 2006 he has a very strong background development and love both the ER model and OO principles. He is also a fan of Agile Methodology and Automation, which he tries to apply everywhere he can, to make sure that "people think, machines do".

This Blog

Syndication

Privacy Statement