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 on SQL Server, Business Intelligence and .NET

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

Davide Mauri - MCP, MCAD, MCDBA, MCT, MVP on SQL Server - has worked with SQL Server since version 6.5, and his interests cover the whole platform, from the Relational Engine to Analysis Services, from architecture definition to performance tuning. He also has a strong knowledge of XML, .NET and the Object Oriented Design principles, which allows him to have the correct vision and experience to handle development of complex business intelligence solutions. Having worked as a Microsoft Certified Teacher for many years, Davide is able to pass all his knowledge to his co-workers, allowing his team to deliver high-quality solutions. He currently works as a Mentor for SolidQ and can be found speaking in many Italian and internationals events.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement