UGISS User Group Italiano SQL Server
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:
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:
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
