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 effective