Polecenie MERGE w TSQL oraz wykorzystanie klauzuli OUTPUT

Polecenie MERGE zostało wprowadzone w Microsoft SQL Server w wersji 2008. Jego właściwością oraz niewątpliwą zaletą dla programisty jest użycie operacji wstawiania, aktualizowania lub usuwania (INSERT, UPDATE, DELETE) danych w tabeli w ramach jednego polecenia MERGE. Dzięki temu nie musimy pisać wyrażeń INSERT, UPDATE, DELETE oddzielnie, lecz całą logikę możemy zawrzeć w jednym poleceniu.

Instrukcja MERGE – wprowadzenie

Najważniejsza zaletą instrukcji MERGE, z punktu widzenia wydajności, jest to, że dane odczytywane są tylko raz. Natomiast przy użyciu trzech oddzielnych poleceń: INSERT, UPDATE, DELETE dane zostaną odczytane łącznie trzy razy – oddzielnie dla każdego polecenia.

Operacja MEGRE wykonuje działania na danej tabeli „TARGET” w oparciu o zbiór danych „SOURCE”, do którego odnosimy się (może to być tabela lub np. wynik zapytania SELECT) poprzez porównanie „TARGET” z „SOURCE” na podstawie określonych przez nas warunków i, w zależności od wyniku tego porównania, zastosowanie jednej z operacji: INSERT, UPDATE, DELETE.

Składnia

MERGE <target_table> [AS TARGET]
USING <table_source> [AS SOURCE]
ON <search_condition>
[WHEN MATCHED
THEN <merge_matched> ]
[WHEN NOT MATCHED [BY TARGET]
THEN <merge_not_matched> ]
[WHEN NOT MATCHED BY SOURCE
THEN <merge_matched> ];

Operator MERGE – do czego służy?

Operator MERGE może służyć do synchronizowania danych pomiędzy tabelami np. kiedy chcemy dodać do tabeli „TARGET” rekordy, które istnieją w porównywanej tabeli „SOURCE” oraz jednocześnie zaktualizować te już istniejące w „TARGET” do stanu z „SOURCE”. Oczywiście – kluczem synchronizującym dane będzie warunek przez nas użyty, po którym rekordy w tabelach mają być sprawdzane.

W powyższym przypadku widzę zastosowanie polecenia MERGE jako elementu procedur zasilających dane do tabel narastająco oraz z jednoczesną możliwością aktualizacji istniejących już w tabeli danych z poprzednich dni do aktualnego stanu – np. jako element procedury wywoływanej automatyczne w ramach procesów ETL.

Dobrym przykładem zastosowania polecenia MERGE może być również synchronizacja danych w tabelach pomiędzy różnymi środowiskami – testowym i produkcyjnym. Dobrą praktyką w takim przypadku będzie użycie, w warunkach porównania tabel „TARGET” i „SOURCE”, kolumny/kolumn, które stanowią klucz główny tabeli. Zapewni to unikalność porównywanych rekordów i uchroni nas przed potencjalnym błędem zdublowania rekordów i, tym samym, „popsuciem” danych w tabeli.

Warto zapamiętać

Stosując zapytanie MERGE należy pamiętać, że:

• W jednym poleceniu MERGE możemy zastosować tylko jedno polecenie INSERT, jedno UPDATE i jedno DELETE. Nie możemy zastosować w ramach jednego MEGRE np. wielu INSERT-ów lub pozostałych funkcji więcej niż jeden raz.
• Przy użyciu instrukcji MERGE należy określić co najmniej jedną z trzech klauzul MATCHED.
• Wyrażenie MERGE kończy się średnikiem.

Przykład zastosowania polecenia MERGE

Dla zaprezentowania działania MERGE posłużę się tabelą ze spisem produktów sprzedawanych w sklepie. W pierwszym kroku stworze tabelę „Products”, która zawiera informację o produktach, które były dotychczas sprzedawane (z unikalnym id dla każdego produktu) oraz informację o nazwie oraz cenie.

Create table Products (
Product_ID int primary key ,
ProductName nvarchar (100) ,
Price money )
 
GO
 
insert into Products
Values
( 1 , 'Herbata' , 10.50 ) ,
( 2 , 'Kawa' , 25.00 ) ,
( 3 , 'Czekolada' , 5.50 ) ,
( 4 , 'Ciastka' ,  3.50 ) ,
( 5 , 'Jogurt' , 2.50 ) ,
( 6 , 'Maslo' , 5.50 )
 
GO

Dodatkowo, tworzę tabelę „Current_Products”, która zawiera aktualizację bieżącej oferty – produkty, które są aktualnie oferowane oraz aktualne ceny.

create table Current_Products (
Product_ID int primary key ,
ProductName nvarchar (100) ,
Current_Price  money )
 
insert into  Current_Products
Values
( 1 , 'Herbata' , 11.50 ) ,
( 2 , 'Kawa' , 28.00 ) ,
( 3 , 'Czekolada' , 5.50 ) ,
( 4 , 'Ciastka czekoladowe' ,  3.50 ) ,
( 7 , 'Ketchup' , 7.00 )
GO

select *
from Products
 
select *
from Current_Products

Zadanie, które chcę wykonać, to zaktualizować tabelę „Products” w oparciu o „Current_Products” w taki sposób, aby zawierała informację tylko o aktualnej ofercie. Zależy mi na tym, aby dla produktów, które powinny zostać w tabeli „Products” sprawdzić, czy mają aktualną cenę oraz nazwę produktu (właściwa dla danego Product_ID). Jeśli nie, to zaktualizować, jeśli pojawiły się nowe produkty w „Current_Products”, to je dodać do „Products”, a jeśli w „Products” znajdują się takie, których już nie ma w aktualnej ofercie, to usunąć z tabeli.

W tym celu wykorzystam polecenie MERGE, które świetnie nadaje się do realizacji tego zadania.

Jako warunku porównania tabel, użyję atrybutu Product_ID, który jednoznacznie identyfikuje rekordy w obu tabelach.

MERGE Products as TARGET
USING Current_Products as SOURCE
ON TARGET.Product_ID = SOURCE.Product_ID
 
WHEN MATCHED and TARGET.ProductName <> SOURCE.ProductName or TARGET.Price <> SOURCE.Current_Price
THEN UPDATE
set TARGET.ProductName = SOURCE.ProductName ,
TARGET.Price = SOURCE.Current_Price
 
WHEN NOT MATCHED BY TARGET THEN
INSERT ( Product_ID , ProductName , Price)
Values ( SOURCE.Product_ID , SOURCE.ProductName , SOURCE.Current_Price )
 
WHEN NOT MATCHED BY SOURCE THEN
DELETE
OUTPUT $action as action ,
inserted.Product_ID as Product_ID,
inserted.ProductName as ProductName_New ,
inserted.Price as Price_New ,
 
deleted.Product_ID as Product_ID ,
deleted.ProductName as ProductName_Old ,
deleted.Price as Price_Old
;

Po uruchomieniu powyższego kodu możemy prześledzić, co zostało dokonane w poniższej tabeli „Products” dla zmodyfikowanych rekordów

MERGE, OUTPUT, MATCHED

W tym przykładzie do polecenia MERGE dodałem również klauzulę OUTPUT – dopisaną po użyciu klauzul MATCHED. Dzięki zastosowaniu tej instrukcji możemy jednocześnie zobaczyć, jakie działania w ramach naszego MERGE zostały wykonane w tabeli „Products”. Sprawdzamy, które rekordy zostały zaktualizowane, a które wstawione lub usunięte Zastosowanie instrukcji MERGE w tabeli ze spisem produktów sprzedawanych w sklepie – kolumna „action”.

Dodatkowo, używając polecenia inserted., mamy wyświetlone informacje o tym, jakie modyfikacje zostały dokonane (powyżej zaznaczone na zielono), a dzięki poleceniu deleted., jak wyglądał stan przed zastosowaniem polecenia MERGE dla zmodyfikowanych rekordów (powyżej zaznaczone na czerwono).

Stan tabeli Products po wykonaniu powyższego polecenia MERGE zawiera listę tylko aktualnie oferowanych produktów wraz z informacją o aktualnych cenach oraz nazwach.

select *
from Products

Instrukcja OUTPUT

Instrukcje OUTPUT możemy stosować również wykonując pojedyncze polecenia INSERT, DELETE lub UPDATE. Wykorzystanie polecenia MERGE razem z OUTPUT, daje nam możliwość kontrolowania dokonywanych zmian i przechwytywania informacji o dokonanych modyfikacjach w tabeli. Przykładowo, informacje o dokonanych zmianach za pomocą polecenia OUTPUT INTO, możemy wstawić do zmiennej tabelarycznej, w celu wyświetlenia lub zarchiwizowania rekordów.

Aby zobrazować powyższy przykład użycia polecenia MERGE, dodatkowo z wyświetleniem informacji o modyfikacji danych, ponownie przygotuję dane w tabelach „Products” oraz „Current_Products” ze stanem produktów do uaktualnienia.

TRUNCATE TABLE Products
 
insert into Products
Values
( 1 , 'Herbata' , 10.50 ) ,
( 2 , 'Kawa' , 25.00 ) ,
( 3 , 'Czekolada' , 5.50 ) ,
( 4 , 'Ciastka' ,  3.50 ) ,
( 5 , 'Jogurt' , 2.50 ) ,
( 6 , 'Maslo' , 5.50 )
 
GO
 
TRUNCATE TABLE Current_Products
 
insert into  Current_Products
Values
( 1 , 'Herbata' , 11.50 ) ,
( 2 , 'Kawa' , 28.00 ) ,
( 3 , 'Czekolada' , 5.50 ) ,
( 4 , 'Ciastka czekoladowe' ,  3.50 ) ,
( 7 , 'Ketchup' , 7.00 )
 
GO

Aby wyświetlić informacje o dokonanych zmianach, deklaruję zmienną tabelaryczną @arch, a następnie wstawiam wyniki polecenia OUTPUT do tej zmiennej, za pomocą polecenia OUTPUT INTO.

declare @arch table
( action varchar(10) ,
Product_ID int ,
ProductName nvarchar (100) ,
Price money );
MERGE Products as TARGET
USING Current_Products as SOURCE
ON TARGET.Product_ID = SOURCE.Product_ID
 
WHEN MATCHED and TARGET.ProductName <> SOURCE.ProductName or TARGET.Price <> SOURCE.Current_Price
THEN UPDATE
set TARGET.ProductName = SOURCE.ProductName ,
TARGET.Price = SOURCE.Current_Price
 
WHEN NOT MATCHED BY TARGET THEN
INSERT ( Product_ID , ProductName , Price)
Values ( SOURCE.Product_ID , SOURCE.ProductName , SOURCE.Current_Price )
 
WHEN NOT MATCHED BY SOURCE THEN
DELETE
OUTPUT $action as action ,
deleted.Product_ID as Product_ID ,
deleted.ProductName as ProductName_Old ,
deleted.Price as Price_Old
 
INTO @arch ;
 
select *
from @arch
where action in ('UPDATE' ,'DELETE' ) ;

W poleceniu SELECT wyświetliłem informacje o stanie zmodyfikowanych rekordów przed zastosowaniem zmian w tabeli Products z informacją, czy była to aktualizacja, czy też usunięcie rekordu z tabeli.

MERGE – przydatne informacje

• Siłą MERGE jest możliwość użycia kilku operacji jednocześnie w ramach jednego polecenia i jednego odczytu. Należy pamiętać, że użycie pojedynczej operacji modyfikacji danych (INSERT, UPDATE, DELETE) poza poleceniem MERGE będzie bardziej wydajne, niż użycie w instrukcji MERGE tylko z np. samym INSERT. Jeśli mamy do wykonania tylko jedno polecenie modyfikacji, przy dużej ilości danych należy się zastanowić nad użyciem takiego polecenia bez zawierania w MERGE.
• W celu poprawienia wydajności polecenia MERGE, przy działaniu na bardzo dużej ilości danych, zalecane jest założenie indeksów w tabelach dla kolumn używanych w warunku złączenia:

1. Założenie unikalnego i pokrywającego indeksu dla kolumn złączenia w tabeli SOURCE.
2. Założenie unikalnego indeksu klastrowego w tabeli TARGET dla kolumn złączenia.

W moim powyższym przykładzie, do warunku złączenia w poleceniu MERGE używam kolumn Product_ID, które zarówno w tabeli „Products”, jak i „Current_Products” stanowią klucz główny, a więc unikalny indeks klastrowy.

• Instrukcja @@ROWCOUNT użyta po poleceniu MERGE zwraca całkowitą liczbę wierszy jakie zostały zmodyfikowane w ramach MERGE, czyli łączną ilości wierszy z wszystkich instrukcji INSERT, UPDATE i DELETE, jakie były zastosowane w MERGE – jedną sumę rekordów wstawionych, zaktualizowanych oraz usuniętych.
• Polecenie MERGE nie jest obsługiwane na Azure SQL Data Warehouse.

Podsumowanie

Dzięki poleceniu MERGE, programiści mogą skuteczniej obsługiwać typowe scenariusze hurtowni danych, takie jak sprawdzanie czy istnieje wiersz, a następnie wykonywanie operacji INSERT, UPDATE lub DELETE.

Poprzez możliwość jednoczesnego zastosowania kilku operacji, instrukcja MERGE może poprawić czytelność kodu (jedno polecenie) oraz wydajność (jeden odczyt). Jednak należy pamiętać, że nie uzyskamy poprawy wydajności stosując MERGE z tylko jednym poleceniem.

Jeżeli polecenie MEGRE będzie wykorzystywane cyklicznie przy dużych ilościach danych, np. w ramach procesów ETL jako element procedury ładującej dane codziennie w celu poprawienia wydajności, należy się zastanowić nad założeniem indeksów w wykorzystywanych tabelach na kolumnach złączenia.

Wykorzystując instrukcję OUTPUT razem z poleceniem MERGE możemy weryfikować wynik działań jakie wykonujemy i przechwytywać informację o dokonanych zmianach, dla procesu kontroli lub archiwizacji danych.

***

Jesli interesują Cię zagadnienia Architektury Lakehouse i koncepcji Data Lake lub zastanawiasz się, jak graficznie zaplanować bazy danych, zachęcamy do zapoznania się z artykułami specjalistów Sii.