Microsoft SQL Server
MERGE

Вступление

Синтаксис

• В соответствии с MSDN - https://msdn.microsoft.com/en-us/library/bb510625.aspx [WITH <common_table_expression> [, ... n]] MERGE [TOP (выражение) [PERCENT]] [INTO] < target_table> [WITH (<merge_hint>)] [[AS] table_alias] ИСПОЛЬЗОВАНИЕ <table_source> ON <merge_search_condition> [КОГДА СООТВЕТСТВУЕТ [AND <clause_search_condition>] THEN <merge_matched>] [... n] [КОГДА НЕ СООТВЕТСТВУЕТ [BY TARGET] [AND <clause_search_condition>] THEN <merge_not_matched>] [КОГДА НЕ СООТВЕТСТВУЕТ ИСТОЧНИКОМ [AND <clause_search_condition>] THEN <merge_matched>] [... n] [<output_clause>] [OPTION (<query_hint> [,. ..n])]; <target_table> :: = {[имя_базы. имя_схемы. | имя_схемы. ] target_table} <merge_hint> :: = {{[<table_hint_limited> [, ... n]] [[,] INDEX (index_val [, ... n])]}} <table_source> :: = {table_or_view_name [ [AS] table_alias] [<tablesample_clause>] [WITH (table_hint [[,] ... n])] | rowset_function [[AS] table_alias] [(bulk_column_alias [, ... n])] | user_defined_function [[AS] table_alias] | OPENXML <openxml_clause> | found_table [AS] table_alias [(column_alias [, ... n])] | <join_table> | <pivoted_table> | <unpivoted_table>} <merge_search_condition> :: = <search_condition> <merge_matched> :: = {UPDATE SET <set_clause> | DELETE} <set_clause> :: = SET {column_name = {выражение | ПО УМОЛЧАНИЮ | NULL} | {udt_column_name. {{property_name = выражение | field_name = выражение} | method_name (argument [, ... n])}} | column_name {.WRITE (выражение, @Offset, @Length)} | @variable = выражение | @variable = column = выражение | column_name {+ = | - = | * = | / = | % = | & = | ^ = | | =} выражение | @variable {+ = | - = | * = | / = | % = | & = | ^ = | | =} выражение | @variable = column {+ = | - = | * = | / = | % = | & = | ^ = | | =} выражение} [, ... n] <merge_not_matched> :: = {INSERT [(column_list)] {VALUES (values_list) | DEFAULT VALUES}} <clause_search_condition> :: = <search_condition> :: = {[NOT] | (<search_condition>)} [{AND | ИЛИ} [NOT] {| (<search_condition>)}] [, ... n] :: = {выражение {= | <> | ! = |

  | > = | ! > | <| <= | ! <} выражение | string_expression [NOT] LIKE string_expression [ESCAPE 'escape_character'] | выражение [NOT] BETWEEN выражение AND выражение | выражение IS [NOT] NULL | CONTAINS ({column | *}, '<contains_search_condition>') | FREETEXT ({column | *}, 'freetext_string') | выражение [NOT] IN (подзапрос | выражение [, ... n]) | выражение {= | <> | ! = | | > = | ! > | <| <= | ! <} {ВСЕ | НЕКОТОРЫЕ | ANY} (подзапрос) | EXISTS (подзапрос)} <output_clause> :: = {[OUTPUT <dml_select_list> INTO {@table_variable | output_table} [(column_list)]] [OUTPUT <dml_select_list>]} <dml_select_list> :: = {<column_name> | scalar_expression} [[AS] column_alias_identifier] [, ... n] <column_name> :: = {DELETED | ВСТАВЬТЕ | from_table_name}. {* | column_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 NOT MATCHED THEN INSERT предотвратит WHEN NOT MATCHED THEN INSERT записей

Оператор слияния требует конечной точки с запятой.

Ограничения:

• WHEN MATCHED не разрешает действие INSERT
• Действие UPDATE может обновлять строку только один раз. Это означает, что условие объединения должно давать уникальные совпадения.

Слияние с использованием источника 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, рассмотрите следующие две таблицы:

1. dbo.Product : эта таблица содержит информацию о продукте, который компания продает в настоящее время

2. dbo.ProductNew : эта таблица содержит информацию о продукте, который компания будет продавать в будущем.

Следующий T-SQL будет создавать и заполнять эти две таблицы

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.Product с таблицей dbo.ProductNew. Вот критерий для этой задачи:

1. Продукт, который существует как в исходной таблице dbo.ProductNew, так и в целевой таблице dbo.Product, обновляется в целевой таблице dbo.Product с новыми новыми продуктами.

2. Любой продукт в dbo.ProductНовая исходная таблица, которая не существует в целевой таблице dob.Product, вставляется в целевую таблицу dbo.Product.

3. Любой продукт в целевой таблице dbo.Product, которая не существует в исходной таблице dbo.ProductNew, должна быть удалена из целевой таблицы 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;