Microsoft SQL Server
MERGE

introduzione

Sintassi

• Come da MSDN - https://msdn.microsoft.com/en-us/library/bb510625.aspx [WITH <espressione_comprensione_condivisa> [, ... n]] MERGE [TOP (espressione) [PERCENT]] [INTO] < target_table> [WITH (<merge_hint>)] [[AS] table_alias] UTILIZZO <table_source> ON <merge_search_condition> [QUANDO ABBINATO [AND <clause_search_condition>] THEN <merge_matched>] [... n] [QUANDO NON ABBINATO [BY TARGET] [AND <clause_search_condition>] THEN <merge_not_matched>] [QUANDO NON ABBINATI DA SOURCE [AND <clause_search_condition>] THEN <merge_matched>] [... n] [<output_clause>] [OPTION (<query_hint> [,. ..n])]; <target_table> :: = {[database_name. nome_schema. | nome_schema. ] target_table} <merge_hint> :: = {{[<table_hint_limited> [, ... n]] [[,] INDICE (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> | derived_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 = {espressione | DEFAULT | NULL} | {udt_column_name. {{property_name = expression | field_name = espressione} | method_name (argomento [, ... n])}} | column_name {.WRITE (expression, @Offset, @Length)} | @variabile = espressione | @variabile = colonna = espressione | column_name {+ = | - = | * = | / = | % = | & = | ^ = | | =} espressione | @variable {+ = | - = | * = | / = | % = | & = | ^ = | | =} espressione | @variable = column {+ = | - = | * = | / = | % = | & = | ^ = | | =} espressione} [, ... n] <merge_not_matched> :: = {INSERT [(column_list)] {VALUES (values_list) | VALORI PREDEFINITI}} <clause_search_condition> :: = <search_condition> :: = {[NOT] | (<search_condition>)} [{AND | OR} [NOT] {| (<search_condition>)}] [, ... n] :: = {espressione {= | <> | ! = |

  | > = | ! > | <| <= | ! <} espressione | string_expression [NOT] LIKE string_expression [ESCAPE 'escape_character'] | espressione [NOT] TRA espressione ed espressione | espressione IS [NOT] NULL | CONTAINS ({column | *}, '<contains_search_condition>') | FREETEXT ({column | *}, 'freetext_string') | espressione [NOT] IN (subquery | expression [, ... n]) | espressione {= | <> | ! = | | > = | ! > | <| <= | ! <} {TUTTI | ALCUNI | QUALSIASI} (sottoquery) | EXISTS (sottoquery)} <output_clause> :: = {[OUTPUT <dml_select_list> INTO {@table_variable | output_table} [(column_list)]] [OUTPUT <dml_select_list>]} <dml_select_list> :: = {<nome_colonna> | scalar_expression} [[AS] column_alias_identifier] [, ... n] <column_name> :: = {DELETED | INSERITO | from_table_name}. {* | column_name} | $ azione

Osservazioni

MERGE per inserire / aggiornare / eliminare

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

Descrizione:

• MERGE INTO targetTable - tabella da modificare
• USING sourceTable - fonte di dati (può essere una tabella o una vista o una funzione con valori di tabella)
• ON ... - condizioni di join tra targetTable e sourceTable .
• WHEN MATCHED : azioni da intraprendere quando viene trovata una corrispondenza
• • AND (targetTable.PKID > 100) - condizioni aggiuntive che devono essere soddisfatte affinché l'azione possa essere intrapresa
• THEN DELETE : elimina il record corrispondente dal targetTable
• THEN UPDATE - aggiorna le colonne del record corrispondente specificato da SET ....
• WHEN NOT MATCHED : le azioni da intraprendere quando la corrispondenza non viene trovata in targetTable
• WHEN NOT MATCHED BY SOURCE : le azioni da intraprendere quando la corrispondenza non viene trovata in sourceTable

Commenti:

Se non è necessaria un'azione specifica, allora ometti la condizione, ad esempio rimuovendo WHEN NOT MATCHED THEN INSERT , impedirai l'inserimento di record

La dichiarazione di unione richiede un punto e virgola.

restrizioni:

• WHEN MATCHED non consente l'azione INSERT
• UPDATE azione UPDATE può aggiornare una riga solo una volta. Ciò implica che la condizione di join deve produrre corrispondenze univoche.

Unisci utilizzando origine 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 usando la tabella delle fonti derivate

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);

Unisci esempio: sincronizza la tabella di origine e di destinazione

Per illustrare la dichiarazione MERGE, considerare le seguenti due tabelle:

1. dbo.Product : questa tabella contiene informazioni sul prodotto che la società sta attualmente vendendo

2. dbo.ProductNew : questa tabella contiene informazioni sul prodotto che la società venderà in futuro.

Il seguente T-SQL creerà e popolerà queste due tabelle

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)

Ora, supponiamo di voler sincronizzare la tabella di destinazione dbo.Product con la tabella dbo.ProductNew. Ecco il criterio per questo compito:

1. I prodotti presenti sia nella tabella di origine dbo.ProductNew che nella tabella di destinazione dbo.Product vengono aggiornati nella tabella di destinazione dbo.Product con nuovi prodotti.

2. Qualsiasi prodotto nella tabella di origine dbo.ProductNew che non esiste nella tabella di destinazione dob.Product viene inserito nella tabella di destinazione dbo.Product.

3. Qualsiasi prodotto nella tabella di destinazione dbo.Product che non esiste nella tabella di origine dbo.ProductNew deve essere eliminato dalla tabella di destinazione dbo.Product. Ecco la dichiarazione MERGE per eseguire questa attività.

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.*;

Nota: il punto e virgola deve essere presente alla fine dell'istruzione MERGE.

Unisci utilizzando EXCEPT

Utilizzare EXCEPT per impedire aggiornamenti ai record non modificati

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;