Home > Articoli > MERGE Statements in Action

MERGE Statements in Action

SQL Server 2008 introduce il supporto al comando MERGE. Questo comando permette di specificare una tabella sorgente (source) ed una tabella destinazione (target o destination), modificando il contenuto della tabella destinazione con i dati della tabella sorgente.

Potranno essere eseguite operazioni DML multiple (INSERT/UPDATE/DELETE) in funzione delle condizioni logiche specificate nelle clausole del comando. SQL Server implementa tutte le caratteristiche del comando MERGE standard ANSI e aggiunge alcune importanti estensioni disponibili solo nel linguaggio T-SQL.

Figura 1 – Merge statement

Il comando MERGE può essere utilizzato in ambienti OLTP, ma anche OLAP; in ambienti transazionali possiamo utilizzarlo per eseguire il merge dei dati da una sorgente esterna ad una tabella esistente (UPSERT). In un data werehouse, il comando MERGE può essere utilizzato per eseguire UPDATE incrementali oppure per processare slowly changing dimensions.

Il comando MERGE viene risolto utilizzando operazioni di Join, che interessano la tabella sorgente e la tabella destinazione. Il predicato ON esprime la condizione di Join che deve essere verificata per collegare le righe della tabella sorgente con le righe della tabella destinazione. Si potrà quindi specificare quale azione avviare quando la riga:

  • Esiste sia nella tabella sorgente che nella tabella destinazione (WHEN MATCHED)
  • Esiste nella tabella sorgente, ma non nella tabella destinazione (WHEN NOT MATCHED [BY TARGET])
  • Esiste nella tabella destinazione, ma non in quella sorgente (WHEN NOT MATCHED BY SOURCE)

L’ultima clausola WHEN NOT MATCHED BY SOURCE rappresenta un’estensione proprietaria del linguaggio T-SQL, non è disponibile nel comando MERGE standard ANSI.

Vediamo ora come il comando MERGE può aiutarci nel caricamento di un inventario di magazzino.

Ipotizziamo di eseguire l’inventario, a rotazione, dei prodotti giacenti a magazzino e di dover aggiornare le giacenze (logiche) memorizzate nella tabella dbo.ProductInventory con le giacenze (fisiche), rilevate dagli operatori, durante l’inventario. Il comando MERGE sarà di grande aiuto per aggiornare i dati contenuti nella tabella dbo.ProductInventory (target). Si dovrà quindi prevedere di:

  • Aggiornare la quantità giacente di un prodotto
  • Eliminare, dalla tabella dbo.ProductInventory, i prodotti non presenti a magazzino (rilevazioni con giacenza uguale a zero)
  • Inserire, nella tabella dbo.ProductInventory, i nuovi prodotti rilevati a magazzino (e non presenti in target)

Procediamo con la creazione della tabella dbo.ProductInventory sul database AdventureWorks ed inseriamo alcuni dati di prova.

use [AdventureWorks];
go

————————————————————————
— Setup table & insert data
————————————————————————

— dbo.ProductInventory
if OBJECT_ID(‘dbo.ProductInventory’, ‘U’) is not null
  drop table dbo.ProductInventory;
go

create table dbo.ProductInventory
(
  ProductID varchar(25) not null
  ,LocationID varchar(20) not null
  ,Quantity decimal(9, 2) not null
  ,ModifiedDate date not null default getdate()
  ,Status bit not null default 1
  ,constraint PK_ProductInventory primary key(ProductID, LocationID)
);

insert into dbo.ProductInventory
(ProductID, LocationID, Quantity, ModifiedDate)
values
(‘ravioli angelo’, ‘b002’, 10, ‘20100101’),
(‘chocolade’,      ‘f015’,  5, ‘20100101’),
(‘ipoh coffee’,    ‘h001’, 70, ‘20100201’),
(‘mascarpone doc’, ‘d214’, 30, ‘20100201’);
go

Consultiamo i dati inseriti, che rappresentano le giacenze (logiche) dei prodotti in magazzino:

select * from dbo.ProductInventory;
go

Figura 2 – Giacenze logiche dei prodotti in magazzino

Ipotizziamo di raccogliere i dati inventariali nella tabella dbo.FrequentInventory, che possiamo creare e popolare con alcuni dati di test attraverso il seguente frammento di codice T-SQL:

— dbo.FrequentInventory
if OBJECT_ID(‘dbo.FrequentInventory’, ‘U’) is not null
  drop table dbo.FrequentInventory;
go

create table dbo.FrequentInventory
(
  ProductID varchar(25) not null
  ,LocationID varchar(20) not null
  ,Quantity decimal(9, 2) not null
  ,ModifiedDate date not null default getdate()
  ,constraint PK_FrequentInventory primary key(ProductID, LocationID)
);
go

insert into dbo.FrequentInventory
(ProductID, LocationID, Quantity, ModifiedDate)
values
(‘CHOCOLADE’,         ‘F015’,  7, ‘20100612’),
(‘GORGONZOLA TELINO’, ‘F001’, 22, ‘20100612’),
(‘SEATTLE CRAB’,      ‘G004’, 80, ‘20100612’),
(‘MASCARPONE DOC’,    ‘D214’,  0, ‘20100201’);

Consultiamo i dati rilevati durante l’inventario:

select * from dbo.FrequentInventory;
go

Figura 3 – Giacenze fisiche rilevate durante l’inventario

 

MERGE Statement

Il primo comando MERGE che esaminiamo è quello che ci permette di aggiornare le righe (target) presenti sia nella tabella sorgente dbo.FrequentInventory che nella tabella destinazione dbo.ProductInventory. Per far questo specifichiamo una azione nella clausola WHEN MATCHED. Dopo aver aggiornato ed interrogato la tabella (target) dbo.ProductInventory, viene eseguito il ROLLBACK della transazione.

Osserviamo che nel comando di UPDATE non è stata specificata la tabella dbo.ProductInventory, in modo esplicito, perché questa è già nota by default (target table).

— MERGE con la clausola WHEN MATCHED
begin tran;
go

select * from dbo.ProductInventory;
go

merge into dbo.ProductInventory as itarget
using dbo.FrequentInventory as isource
  on ((itarget.ProductId = isource.ProductId)
     and (itarget.LocationId = isource.LocationId))
when matched then
  update /* dbo.ProductInventory */ set
    itarget.Quantity = isource.Quantity
    ,itarget.ModifiedDate = isource.ModifiedDate;

select * from dbo.ProductInventory;
go

rollback tran;
go

Otteniamo l’output illustrato in figura 4.

Figura 4 – Output MERGE con clausola WHEN MATCHED

In presenza della sola clausola WHEN MATCHED, il comando MERGE viene espresso con una INNER JOIN che possiamo vedere nel piano di esecuzione illustrato nella figura seguente.

Figura 5 – Execution plan per il comando MERGE con clausola WHEN MATCHED

 

Clausola WHEN NOT MATCHED

Implementiamo un altro requisito, ossia la possibilità di inserire le righe della sorgente dati che non trovano corrispondenza nella tabella destinazione dbo.ProductInventory. Modifichiamo quindi il precedente comando MERGE per aggiungere la clausola WHEN NOT MATCHED alla quale assoceremo un’operazione di INSERT. Come per il comando di UPDATE, la sintassi del comando INSERT è molto simile a quella che utilizziamo normalmente ad eccezione del nome della tabella, oggetto degli inserimenti, che non viene specificata perché la tabella di destinazione è già nota nella clausola INTO.

Eseguiamo il seguente frammento di codice, anche in questo caso viene eseguito il ROLLBACK della transazione.

— MERGE con le clausole WHEN MATCHED e WHEN NOT MATCHED
begin tran;
go

select * from dbo.ProductInventory;
go

merge into dbo.ProductInventory as itarget
using dbo.FrequentInventory as isource
  on ((itarget.ProductId = isource.ProductId)
     and (itarget.LocationId = isource.LocationId))
when matched then
  update /* dbo.ProductInventory */ set
    itarget.Quantity = isource.Quantity
    ,itarget.ModifiedDate = isource.ModifiedDate
when not matched then
  insert /* into dbo.ProductInventory */
  (
    ProductId
    ,LocationId
    ,Quantity
    ,ModifiedDate
  )
  values
  (
    isource.ProductId
    ,isource.LocationId
    ,isource.Quantity
    ,isource.ModifiedDate
  );

select * from dbo.ProductInventory;
go

rollback tran;
go

Otteniamo l’output illustrato in figura 6, osserviamo che i prodotti “GORGONZOLA TELINO” e “SEATTLE CRAB” sono stati inseriti nella tabella target dbo.ProductInventory per effetto dell’azione specificata nella clausola WHEN NOT MATCHED.

Figura 6 – Output MERGE con clausole WHEN MATCHED e WHEN NOT MATCHED

Pensiamo ora a come avremmo implementato la soluzione di questo problema nelle precedenti versioni di SQL Server. Probabilmente avremmo scritto due comandi separati, un UPDATE ed un INSERT come quelli riportati di seguito, che avrebbero permesso di ottenere lo stesso output di figura 6.

begin tran
go

select * from dbo.ProductInventory;
go

update
  itarget
set
  itarget.Quantity = isource.Quantity
  ,itarget.ModifiedDate = isource.ModifiedDate
from
  dbo.ProductInventory itarget
join
  dbo.FrequentInventory isource
    on (itarget.ProductID = isource.ProductID) and
       (itarget.LocationID = isource.LocationID);
go

insert into dbo.ProductInventory
(
  ProductID, LocationID, Quantity, ModifiedDate
)
select
  isource.ProductID
  ,isource.LocationID
  ,isource.Quantity
  ,isource.ModifiedDate
from
  dbo.FrequentInventory as isource
where
  not exists (select *
              from
                dbo.ProductInventory as itarget
              where
                (isource.ProductID = itarget.ProductID)
                and (isource.LocationID = itarget.LocationID)
             );
go

select * from dbo.ProductInventory;
go

rollback tran
go

Il vantaggio che deriva dall’utilizzo del comando MERGE è nell’accesso ai dati, con MERGE i dati vengono acceduti una sola volta, contro il doppio accesso nel caso dei due comandi separati di INSERT e UPDATE. Il comando MERGE viene eseguito con una operazione atomica, che non necessità di transazioni esplicite. In aggiunta, l’operazione di MERGE ci protegge da eventuali UPDATE non deterministici, nel senso che, se la stessa riga viene aggiornata più di una volta, l’intero comando di MERGE fallisce.

 

Aggiungiamo un predicato

Le clausole WHEN supportano la presenza dell’operatore AND seguito da un predicato, la clausola assumerà quindi la forma: WHEN MATCHED AND <predicato> THEN <azione>. Aggiungendo l’operatore AND, l’azione racchiusa nella clausola verrà eseguita solo se risulteranno essere vere entrambe le condizioni, quella specificata nel predicato ON e quella specificata nel predicato AND.

Il seguente comando MERGE permette di aggiornare le righe nella tabella target solo se gli attributi non chiave sono diversi da quelli sorgente. Con questa tecnica, le performance migliorano e preveniamo l’attivazione di eventuali trigger.

— MERGE con operatore AND nella clausola WHEN MATCHED
begin tran;
go

select * from dbo.ProductInventory;
go

merge into dbo.ProductInventory as itarget
using dbo.FrequentInventory as isource
  on ((itarget.ProductId = isource.ProductId)
     and (itarget.LocationId = isource.LocationId))
when matched AND

  ((itarget.Quantity <> isource.Quantity)
   or (itarget.ModifiedDate <> isource.ModifiedDate)) then

  update set
    itarget.Quantity = isource.Quantity
    ,itarget.ModifiedDate = isource.ModifiedDate
when not matched then
  insert
  (
    ProductId
    ,LocationId
    ,Quantity
    ,ModifiedDate
  )
  values
  (
    isource.ProductId
    ,isource.LocationId
    ,isource.Quantity
    ,isource.ModifiedDate
  );

select * from dbo.ProductInventory;
go

rollback tran;
go

 

Clausole WHEN MATCHED multiple

Il comando MERGE supporta al più due clausole WHEN MATCHED con l’obbligo di specificare nella prima clausola l’operatore aggiuntivo AND, che opzionalmente può essere specificato anche nella seconda clausola. Quando vengono specificate due clausole WHEN MATCHED, SQL Server esegue l’azione indicata nella prima clausola solo se risultano essere verificate le condizioni specificate nel predicato ON e nel predicato aggiuntivo AND. Se l’espressione logica nel predicato ON risulta essere vera, ma l’espressione nel predicato aggiuntivo AND della prima clausola risulta essere falsa o unknown, SQL Server valuterà la seconda clausola WHEN MATCHED e l’azione indicata nella prima clausola non verrà applicata.

L’utilizzo multiplo delle clausole WHEN MATCHED è di aiuto per implementare il seguente requisito: quando l’inventario rileva un prodotto con giacenza uguale a zero (= non presente a magazzino), la rispettiva riga, nella tabella destinazione, deve essere eliminata. Il seguente comando MERGE implementa anche questo requisito:

— MERGE con WHEN MATCHED multipli
begin tran;
go

select * from dbo.ProductInventory;
go

merge into dbo.ProductInventory as itarget
using dbo.FrequentInventory as isource
  on ((itarget.ProductId = isource.ProductId)
     and (itarget.LocationId = isource.LocationId))
when matched AND

  (isource.Quantity <> 0)
  and ((itarget.Quantity <> isource.Quantity)
       or (itarget.ModifiedDate <> isource.ModifiedDate)) then

  update set
    itarget.Quantity = isource.Quantity
    ,itarget.ModifiedDate = isource.ModifiedDate
when matched AND

  (isource.Quantity = 0) then

  delete

when not matched then
  insert
  (
    ProductId
    ,LocationId
    ,Quantity
    ,ModifiedDate
  )
  values
  (
    isource.ProductId
    ,isource.LocationId
    ,isource.Quantity
    ,isource.ModifiedDate
  );

select * from dbo.ProductInventory;
go

rollback tran;
go

L’output ottenuto è illustrato in figura 7, in particolare osserviamo che la riga relativa al prodotto “mascarpone doc” è stata eliminata dalla tabella target dbo.ProductInventory per effetto dell’azione (DELETE) specificata nella seconda clausola WHEN MATCHED.

Figura 7 – Output MERGE con clausole WHEN MATCHED multiple

 

Estensioni proprietarie del linguaggio T-SQL

Il comando MERGE, solo nel linguaggio T-SQL, supporta una terza clausola chiamata WHEN NOT MATCHED BY SOURCE, che permette di indicare una azione da eseguire quando ad una riga nella tabella target non corrisponde nessuna riga nella tabella source. La terza clausola WHEN NOT MATCHED BY SOURCE ci aiuta ad implementare un altro requisito richiesto nella soluzione: quando una riga nella tabella destinazione non trova corrispondenza nella tabella sorgente aggiornare il campo Status impostando il valore 0. Il seguente frammento di codice T-SQL rappresenta un esempio di utilizzo della terza clausola del comando MERGE.

— MERGE con terza clausola WHEN NOT MATCHED BY SOURCE
begin tran;
go

select * from dbo.ProductInventory;
go

merge into dbo.ProductInventory as itarget
using dbo.FrequentInventory as isource
  on ((itarget.ProductId = isource.ProductId)
     and (itarget.LocationId = isource.LocationId))
when matched AND
  (isource.Quantity <> 0)
  and ((itarget.Quantity <> isource.Quantity)
       or (itarget.ModifiedDate <> isource.ModifiedDate)) then
  update set
    itarget.Quantity = isource.Quantity
    ,itarget.ModifiedDate = isource.ModifiedDate
when matched AND
  (isource.Quantity = 0) then
  delete
when not matched then
  insert
  (
    ProductId
    ,LocationId
    ,Quantity
    ,ModifiedDate
  )
  values
  (
    isource.ProductId
    ,isource.LocationId
    ,isource.Quantity
    ,isource.ModifiedDate
  )
when not matched by source then
  update set
    status = 0;

select * from dbo.ProductInventory;
go

rollback tran;
go

L’output ottenuto è illustrato in figura 8, in particolare osserviamo i valori della colonna Status per i prodotti “ipoh coffee” e “ravioli angelo” non presenti nella tabella dbo.FrequentInventory.

Figura 8 – Output MERGE con clausola WHEN NOT MATCHED BY SOURCE

Chi è Sergio Govoni

Sergio Govoni è laureato in Scienze e Tecnologie Informatiche. Da oltre 16 anni lavora presso una software house che produce un noto sistema ERP, distribuito a livello nazionale ed internazionale, multi azienda client/server su piattaforma Win32. Attualmente si occupa di progettazione e analisi funzionale, coordina un team di sviluppo ed è responsabile tecnico di prodotto. Lavora con SQL Server dalla versione 7.0 e si è occupato d'implementazione e manutenzione di database relazionali in ambito gestionale, ottimizzazione delle prestazioni e problem solving. Nello staff di UGISS si dedica alla formazione e alla divulgazione in ambito SQL Server e tecnologie a esso collegate, scrivendo articoli e partecipando come speaker ai workshop e alle iniziative del primo e più importante User Group Italiano sulla tecnologia SQL Server. Ha conseguito la certificazione MCP, MCTS SQL Server. Per il suo contributo nelle comunità tecniche e per la condivisione della propria esperienza con altri, dal 2010 riceve il riconoscimento SQL Server MVP (Microsoft Most Valuable Professional). Nel corso dell'anno 2011 ha contribuito alla scrittura del libro SQL Server MVP Deep Dives Volume 2 (http://www.manning.com/delaney/).

Leggi Anche

Automazione delle attività di manutenzione in Azure SQL Database (2 Parte)

Introduzione Nell’articolo Automazione delle attività di manutenzione in Azure SQL Database abbiamo descritto le attività …