Microsoft SQL Server
マージ

前書き

構文

• MSDNに準拠 - https://msdn.microsoft.com/en-us/library/bb510625.aspx [WITH <common_table_expression> [、... n]] MERGE [TOP（式）[PERCENT]] [INTO] < MATCHED [AND <clause_search_condition>] THEN <merge_matched>] [...] [マッチしたとき[BY] [マッチしたとき] THEN <merge_matched>] [THEN <merge_matched>] THEN <merge_match_condition>] THEN <merge_not_matched>] [ソースと一致しないときは[AND <clause_search_condition>] THEN <merge_matched>] [...] [<output_clause>] [OPTION（<query_hint> [、。 ..n]）]; <target_table> :: = {[database_name。 schema_name。 | schema_name。 {{...}}}}}} <table_source> :: = {table_or_view_name [、...]）{index_val [、... n]）]}} [AS] table_alias] [<tablesample_clause>] [WITH（table_hint [[、] ... n]）] | rowset_function [[AS] table_alias] [（bulk_column_alias [、... n]）] | user_defined_function [[AS]テーブル_エイリアス] | OPENXML <openxml_clause>派生テーブル[AS] table_alias [（column_alias [、... n]）] | <joined_table> | <pivoted_table> | <unpivoted_table>} <merge_search_condition> :: = <search_condition> <merge_matched> :: = {UPDATE SET <set_clause> | DELETE} <set_clause> :: = SET {column_name = {式| DEFAULT | | NULL} {udt_column_name。{{プロパティ名=式| field_name =式} | method_name（引数[、... n]）}} | column_name {.WRITE（式、@Offset、@Length）} | @variable = expression | @variable = column = expression | column_name {+ = | - = | * = | / = | ％= | ＆= | ^ = | | =}式| @variable {+ = | - = | * = | / = | ％= | ＆= | ^ = | | =}式| @variable =列{+ = | - = | * = | / = | ％= | ＆= | ^ = | | =}式} [、... n] <merge_not_matched> :: = {INSERT [（列リスト）] {VALUES（値リスト）|デフォルト値}} <clause_search_condition> :: = <search_condition> :: = {[NOT] | （<search_condition>）} [{AND | OR} [NOT] {| （<search_condition>）}] [、... n] :: = {式{= | <> | ！ = |

  | > = | ！ > | <| <= | ！ <}式| string_expression [NOT] LIKE文字列式[ESCAPE 'escape_character'] | expression [NOT] BETWEEN式AND式|式は[NOT] NULLです。 CONTAINS（{column | *}、 '<contains_search_condition>'）| FREETEXT（{column | *}、 'freetext_string'）|式[NOT] IN（副問合せ|式[、... n]）|式{= | <> | ！ = | | > = | ！ > | <| <= | ！ <} {すべて| SOME | ANY}（サブクエリ）| EXISTS（サブクエリ）} <output_clause> :: = {[OUTPUT <dml_select_list> INTO {@table_variable | output_table} [（列リスト）]] [OUTPUT <dml_select_list>]} <dml_select_list> :: = {<列名> | {[AS] column_alias_identifier] [、... n] <column_name> :: = {DELETED | INSERTED | from_table_name}。 {* | | $アクション

備考

挿入/更新/削除するMERGE

MERGE INTO targetTable

USING sourceTable 
ON (targetTable.PKID = sourceTable.PKID)

WHEN MATCHED AND (targetTable.PKID > 100) THEN
    DELETE

WHEN MATCHED AND (targetTable.PKID <= 100) THEN 
    UPDATE SET 
        targetTable.ColumnA = sourceTable.ColumnA, 
        targetTable.ColumnB = sourceTable.ColumnB

WHEN NOT MATCHED THEN
    INSERT (ColumnA, ColumnB) VALUES (sourceTable.ColumnA, sourceTable.ColumnB);

WHEN NOT MATCHED BY SOURCE THEN
    DELETE
; --< Required

説明：

• MERGE INTO targetTable - 変更されるテーブル
• USING sourceTable - データのソース（テーブルまたはビューまたはテーブル値関数）
• ON ... - targetTableとsourceTable間の条件を結合します。
• WHEN MATCHED - 一致するものが見つかったときに実行するアクション
• • AND (targetTable.PKID > 100) - アクションを実行するために満たす必要のある追加の条件
• THEN DELETE - targetTableから一致するレコードを削除する
• THEN UPDATE - SET ....指定された一致するレコードの列を更新します。
• WHEN NOT MATCHED - targetTableに一致するものが見つからない場合に実行するアクション
• WHEN NOT MATCHED BY SOURCEマッチしないとき - sourceTableにマッチが見つからないときに取るアクション

コメント：

特定のアクションが必要でない場合は、条件を省略します。たとえば、削除した場合はWHEN NOT MATCHED THEN INSERTはレコードが挿入されないようにします。

マージステートメントはセミコロンを終了する必要があります。

制限事項：

• WHEN MATCHEDがINSERTアクションを許可しないとき
• UPDATEアクションは行を1回だけ更新できます。これは、結合条件が一意の一致を生成しなければならないことを意味します。

CTEソースを使用したマージ

WITH SourceTableCTE AS
(
    SELECT * FROM SourceTable
)
MERGE  
 TargetTable AS target
USING SourceTableCTE AS source  
ON (target.PKID = source.PKID)
WHEN MATCHED THEN     
    UPDATE SET target.ColumnA = source.ColumnA
WHEN NOT MATCHED THEN
    INSERT (ColumnA) VALUES (Source.ColumnA);

派生元テーブルを使用するMERGE

MERGE INTO TargetTable  AS Target  
USING (VALUES (1,'Value1'), (2, 'Value2'), (3,'Value3'))  
       AS Source (PKID, ColumnA)  
ON Target.PKID = Source.PKID 
WHEN MATCHED THEN 
    UPDATE SET target.ColumnA= source.ColumnA
WHEN NOT MATCHED THEN
    INSERT (PKID, ColumnA) VALUES (Source.PKID, Source.ColumnA);

マージの例 - ソーステーブルとターゲットテーブルの同期

MERGE声明を説明するには、次の2つの表を検討してください。

1. dbo.Product ：この表には、会社が現在販売している製品に関する情報が含まれています

2. dbo.ProductNew ：この表には、将来販売される予定の製品に関する情報が含まれています。

次のT-SQLは、これら2つのテーブルを作成して設定します

IF OBJECT_id(N'dbo.Product',N'U') IS NOT NULL 
DROP TABLE dbo.Product
GO

CREATE TABLE dbo.Product (
ProductID INT PRIMARY KEY,
ProductName NVARCHAR(64),
PRICE MONEY
)

IF OBJECT_id(N'dbo.ProductNew',N'U') IS NOT NULL 
DROP TABLE dbo.ProductNew
GO

CREATE TABLE dbo.ProductNew (
ProductID INT PRIMARY KEY,
ProductName NVARCHAR(64),
PRICE MONEY
)

INSERT INTO dbo.Product VALUES(1,'IPod',300)
,(2,'IPhone',400)
,(3,'ChromeCast',100)
,(4,'raspberry pi',50)

INSERT INTO dbo.ProductNew VALUES(1,'Asus Notebook',300)
,(2,'Hp Notebook',400)
,(3,'Dell Notebook',100)
,(4,'raspberry pi',50)

ここで、dbo.ProductNewテーブルとdbo.Product Target Tableを同期させたいとします。このタスクの基準は次のとおりです。

1. dbo.ProductNewソーステーブルとdbo.Productターゲットテーブルの両方に存在する製品は、dbo.Productターゲットテーブルで新しい新しい製品で更新されます。

2. dbo.ProductNewソース表内のdob.Productターゲット表に存在しない製品は、dbo.Productターゲット表に挿入されます。

3. dbo.ProductNewソーステーブルに存在しないdbo.Productターゲットテーブル内の製品は、dbo.Productターゲットテーブルから削除する必要があります。このタスクを実行するためのMERGEステートメントを次に示します。

MERGE dbo.Product AS SourceTbl 
USING dbo.ProductNew AS TargetTbl ON (SourceTbl.ProductID = TargetTbl.ProductID)
WHEN MATCHED 
            AND SourceTbl.ProductName <> TargetTbl.ProductName
            OR SourceTbl.Price <> TargetTbl.Price
    THEN UPDATE SET SourceTbl.ProductName = TargetTbl.ProductName,
                SourceTbl.Price = TargetTbl.Price
WHEN NOT MATCHED 
    THEN INSERT (ProductID, ProductName, Price)
         VALUES (TargetTbl.ProductID, TargetTbl.ProductName, TargetTbl.Price)
WHEN NOT MATCHED BY SOURCE 
    THEN DELETE
OUTPUT $action, INSERTED.*, DELETED.*;

注：MERGEステートメントの最後にセミコロンが必要です。

EXCEPTを使用してマージする

EXCEPTを使用して、変更されていないレコードへの更新を防止する

MERGE TargetTable targ
USING SourceTable AS src
    ON src.id = targ.id
WHEN MATCHED
    AND EXISTS (
        SELECT src.field
        EXCEPT
        SELECT targ.field
        )
    THEN
        UPDATE
        SET field = src.field
WHEN NOT MATCHED BY TARGET
    THEN
        INSERT (
            id
            ,field
            )
        VALUES (
            src.id
            ,src.field
            )
WHEN NOT MATCHED BY SOURCE
    THEN
        DELETE;