[MSSQL]Merge Syntax

MERGE是SQL Server 2008的新語法，根據與源表的連接結果，對目標表執行插入，更新或刪除操作。
參考網址

範例

• 建立範例Table

CREATE TABLE City
(
    ID INTEGER PRIMARY KEY NOT NULL,
    CityName VARCHAR(100) NOT NULL,
    StateName VARCHAR(100) NOT NULL
)
GO

• 通常我會搭配User Defined Types 一起用，TypeName: “TableName + Table”

CREATE TYPE [dbo].[CityTable] AS TABLE 
(
    ID INTEGER PRIMARY KEY NOT NULL,
    CityName VARCHAR(100) NOT NULL,
    StateName VARCHAR(100) NOT NULL
);
GO

GRANT EXECUTE
    ON TYPE::[dbo].[CityTable] TO PUBLIC;
GO

• Merge Insert、Update、Delete SP，根據需求選擇使用，MATCHED Not MATCHED後面也可以加條件

CREATE PROCEDURE [dbo].[NSP_City_Merge]
    @CityTable    CityTable    READONLY
AS
    MERGE City AS T     -- 目標Table
    USING @CityTable AS S  -- 資料來源
    ON
        T.ID = S.ID  -- 判斷條件
    WHEN NOT MATCHED THEN -- 也可加條件
        INSERT
        (
            ID,
            CityName,
            StateName
        )
        VALUES
        (
            S.ID,
            S.CityName,
            S.StateName
        )
    WHEN MATCHED THEN -- 也可加條件
        UPDATE SET StateName = S.StateName, CityName = S.CityName
    WHEN NOT MATCHED BY SOURCE THEN  -- 也可加條件
        DELETE
    OUTPUT Inserted.*;
RETURN 0
GO
GRANT EXECUTE
    ON OBJECT::[dbo].[NSP_City_Merge] TO PUBLIC
    AS [dbo];
GO

• Test

DECLARE @CityTable CityTable

INSERT INTO @CityTable 
VALUES(1, 'Taipei', 'Taiwan'),
(2, 'Taichung', 'Taiwan')

EXEC NSP_City_Merge @CityTable