[SQL2012] Sample of the MERGE called UPSERT

/************************************************************************************************
-- Title : [SQL2012] Sample of the MERGE called UPSERT

-- Reference : www.sergeyv.com
-- Key word : sql server merge upsert
************************************************************************************************/

-- It's not work under the SQL Server 2005.
-- You have to execute merge command OVER SQL Server version 2008.

-- Initialization

use tempdb;

go

DROP TABLE ttt;

-- creation table

CREATE TABLE ttt

( Id        INT    IDENTITY(1,1) NOT NULL

, Data    VARCHAR(50)

, CONSTRAINT PK_ttt PRIMARY KEY(id)

);

-- inserting data

INSERT INTO ttt (data) VALUES ('data1');

-- confirming data

SELECT * FROM ttt;

-- executing upsert AS a insert

MERGE INTO ttt

USING 

    (SELECT 'data_searched' AS Search_Col) AS SRC

    ON ttt.Data = SRC.Search_Col

WHEN MATCHED THEN

    UPDATE SET

    Data = 'data_searched_updated'

WHEN NOT MATCHED THEN

    INSERT (Data)

    VALUES (SRC.Search_Col);    

-- confirming inserted data

SELECT * FROM ttt;

-- executing upsert AS a update

MERGE INTO ttt

USING 

    (SELECT 'data_searched' AS Search_Col) AS SRC

    ON ttt.Data = SRC.Search_Col

WHEN MATCHED THEN

    UPDATE SET

    Data = 'data_searched_updated'

WHEN NOT MATCHED THEN

    INSERT (Data)

    VALUES (SRC.Search_Col);    

-- confirming inserted data

SELECT * FROM ttt;