Lekcja 28. Instrukcja MERGE

Instrukcja MERGE pozwala scalić dane z tabeli źródłowej do tabeli docelowej. Instrukcja ta ma wiele praktycznych użyć w scenariuszach liniowych OLTP i hurtowniach danych. Jako przykład dla OLTP załóżmy, że mamy tabelę która nie jest aktualizowana bezpośrednio przez naszą aplikację, zamiast tego aktualizujemy ją różnicowo co jakiś określony okres czasu. Pierw ładujemy różnice do tabeli tymczasowej po czym używamy jej jako tabeli źródłowej do scalenia danych w tabeli docelowej. Jako przykład scenariusza w hurtowni danych załóżmy, że chcemy zachować zagregowany widok danych w hurtowni danych. Używając instrukcji MERGE możemy zapisać zmiany do tabeli szczegółów w postaci zagregowanej.

Użycie instrukcji MERGE

Instrukcja MERGE pozwala na scalenie danych z tabeli źródłowej lub wyrażenia tablicowego do tabeli docelowej. Ogólna postać instrukcji MERGE ma postać:

MERGE INTO <target table> AS TGT
USING <SOURCE TABLE> AS SRC
ON <merge predicate>
WHEN MATCHED [AND <predicate>] -- two clauses allowed:
THEN <action> -- one with UPDATE one with DELETE
WHEN NOT MATCHED [BY TARGET] [AND <predicate>] -- one clause allowed:
THEN INSERT... –- if indicated, action must be INSERT
WHEN NOT MATCHED BY SOURCE [AND <predicate>] -- two clauses allowed:
THEN <action>; -- one with UPDATE one with DELETE

Poszczególne klauzule pełnią następujące role:

MERGE INTO <target table> - klauzula ta definiuje docelową tabelę operacji. Tabela ta może mieć nadany alias.
USING <source table> - ta klauzula definiuje tabelę żródłową lub wyrażenie tablicowe które będzie źródłem scalania. Można nadać jej alias. Wynikiem tej klauzuli musi być tabela.
ON <merge predicate> - w tej klauzuli definiujemy predykat łączący tabelę docelową ze źródłową.
WHEN MATCHED [AND <predicate>] THEN <action> - klauzula ta definiuje akcję jaka ma być wykonana kiedy tabela źródłowa i docelowa są zgodne wg predykatu zawartego w klauzuli ON. Ponieważ wiersz w tabeli docelowej istnieje to operacja INSERT jest niedopuszczalna. Dopuszczalne są natomiast akcje UPDATE i DELETE. Jeżeli chcemy dokonać różnych akcji w zależności od dodatkowych warunków to można użyć dwóch klauzul WHEN MATCHED, każda z innym dodatkowym predykatem.
WHEN NOT MATCHED [BY TARGET] [AND <predicate>] THEN <action> - ta klauzula definiuje akcję wykonywaną kiedy wiersz źródłowy nie pasuje do wiersza docelowego. Ponieważ wiersz docelowy nie istnieje to jedyną akcją dopuszczalną jest INSERT. W tej klauzuli można również dodać dodatkowy predykat definiujący inną akcję.
WHEN NOT MATCHED BY SOURCE [AND <predicate>] THEN <action> - ta klauzula definiuje akcję wykonywaną w momencie kiedy wiersz docelowy istnieje a nie ma go w tabeli źródłowej. Ponieważ wiersz w tabeli docelowej istnieje to nie można wykonać akcji INSERT. Jeżeli chcemy dokonać różnych akcji w zależności od dodatkowych warunków to można użyć dwóch klauzul WHEN NOT MATCHED, każda z innym dodatkowym predykatem.

Przykłady w tej lekcji będą działały na tabeli Sales.MyOrders i sekwencji Sales.SeqOrderID. Oto kod do ich utworzenia:

IF OBJECT_ID('Sales.MyOrders') IS NOT NULL
DROP TABLE Sales.MyOrders;

IF OBJECT_ID('Sales.SeqOrderIDs') IS NOT NULL
DROP SEQUENCE Sales.SeqOrderIDs;

CREATE SEQUENCE Sales.SeqOrderIDs AS INT MINVALUE 1 CYCLE;

CREATE TABLE Sales.MyOrders (
orderid INT NOT NULL
CONSTRAINT PK_MyOrders_orderid
PRIMARY KEY CONSTRAINT DFT_MyOrders_orderid
DEFAULT(NEXT VALUE FOR Sales.SeqOrderIDs)
,custid INT NOT NULL
CONSTRAINT CHK_MyOrders_custid CHECK (custid > 0)
,empid INT NOT NULL
CONSTRAINT CHK_MyOrders_empid CHECK (empid > 0)
,orderdate DATE NOT NULL
);

Załóżmy, że potrzebujemy zdefiniować procedurę składowaną lub skrypt z parametrami akceptujący na wejściu atrybuty zamówienia. jeżeli zamówienie z podanym identyfikatorem istnieje w tabeli Sales.MyOrders to trzeba je zmodyfikować, jeżeli nie to trzeba dodać nowe. Ponieważ w poprzednich lekcjach nie zostały jeszcze omówione procedury składowane to użyty zostanie skrypt z definicją zmiennych lokalnych. Pierwszym krokiem jest zdefiniowanie tabeli źródłowej i docelowej instrukcji MERGE. tabela docelowa to Sales.MyOrders. Źródłem może być tabela lub wyrażenie tablicowe. W tym wypadku użyjemy zdefiniowanych parametrów opisujących zamówienie. Aby uzyskać z nich wyrażenie tablicowe wystarczy użyć zapytania SELECT bez klauzuli FROM lub wstawienie ich do tabeli tymczasowej np:

DECLARE
@orderid AS INT = 1,
@custid AS INT = 1,
@empid AS INT = 2,
@orderdate AS DATE = '20120620';
SELECT *
FROM (SELECT @orderid, @custid, @empid, @orderdate )
AS SRC( orderid, custid, empid, orderdate );

Lub:

DECLARE
@orderid AS INT = 1,
@custid AS INT = 1,
@empid AS INT = 2,
@orderdate AS DATE = '20120620';
SELECT *
FROM (VALUES(@orderid, @custid, @empid, @orderdate))
AS SRC( orderid, custid, empid, orderdate);

Poniżej przedstawiona jest instrukcja MERGE która aktualizuje tabelę kiedy wiersz z podanym identyfikatorem istnieje lub wstawia nowy wiersz:

DECLARE @orderid AS INT = 1
,@custid AS INT = 1
,@empid AS INT = 2
,@orderdate AS DATE = '20120620';

MERGE INTO Sales.MyOrders WITH (HOLDLOCK) AS TGT
USING (
VALUES (
@orderid
,@custid
,@empid
,@orderdate
)
) AS SRC(orderid, custid, empid, orderdate)
ON SRC.orderid = TGT.orderid
WHEN MATCHED
THEN
UPDATE
SET TGT.custid = SRC.custid
,TGT.empid = SRC.empid
,TGT.orderdate = SRC.orderdate
WHEN NOT MATCHED
THEN
INSERT
VALUES (
SRC.orderid
,SRC.custid
,SRC.empid
,SRC.orderdate
);

Zapobieganie konfliktom w instrukcji MERGE

Załóżmy sytuację w której klucz K jeszcze nie istnieje w tabeli docelowej. Dwa procesy P1 i P2 używają instrukcji MERGE w tym samym czasie z tym samym źródłowym kluczem K. Jest możliwa dla MERGE sytuacja taka, że P1 wstawi nowy wiersz z kluczem K do tabeli docelowej w czasie kiedy instrukcja MERGE w P2 będzie sprawdzała czy taki klucz istnieje i spróbuje dodać wiersz. W tej sytuacji wstawianie w P2 nie wykona się z powodu naruszenia klucza podstawowego. Aby zapobiec takiemu zdarzeniu trzeba użyć wskazówki SERIALIZABLE lub HOLDLOCK. Instrukcja MERGE wymaga zadeklarowania jednej klauzuli. Nie trzeba zawsze określać klauzul WHEN MATCHED i WHEN NOT MATCHED. Ponieważ akcja UPDATE jest marnotrawstwem zasobów kiedy wiersze źródłowe i docelowe są identyczne. Istnieje sposób na zapobieganie takim aktualizacjom kiedy nie ma rzeczywistej zmiany wartości. Klauzule WHEN w instrukcji MERGE pozwalają na definicję dodatkowego predykatu. Można dodać predykat który sprawdzi czy jakakolwiek z wartości w wierszu uległa zmianie:

DECLARE
@orderid AS INT = 1,
@custid AS INT = 1,
@empid AS INT = 2,
@orderdate AS DATE = '20120620';
MERGE INTO Sales.MyOrders WITH (HOLDLOCK) AS TGT
USING (VALUES(@orderid, @custid, @empid, @orderdate))
AS SRC( orderid, custid, empid, orderdate)
ON SRC.orderid = TGT.orderid
WHEN MATCHED AND ( TGT.custid <> SRC.custid
OR TGT.empid <> SRC.empid
OR TGT.orderdate <> SRC.orderdate) THEN UPDATE
SET TGT.custid = SRC.custid,
TGT.empid = SRC.empid,
TGT.orderdate = SRC.orderdate
WHEN NOT MATCHED THEN INSERT
VALUES(SRC.orderid, SRC.custid, SRC.empid, SRC.orderdate);

MERGE i NULL

Kiedy sprawdzamy czy wartości kolumn tabeli źródłowej i docelowej są różne za pomocą operatora <> to nie sprawdzane są wartości NULL. Jeżeli wartość NULL może występować w kolumnach to trzeba dodać dodatkowe warunki sprawdzające, np predykat:

TGT.custid = SRC.custid
OR (TGT.custid IS NULL AND SRC.custid IS NOT NULL)
OR (TGT.custid IS NOT NULL AND SRC.custid IS NULL)

Ciekawą informacją o użyciu klauzuli USING w definicji instrukcji MERGE jest to, że jest ona zaprojektowana jak klauzula FROM zapytania SELECT. Oznacza to, że można używać operatorów tabelarycznych jak JOIN, APPLY, PIVOT i UNIPIVOT oraz wyrażeń tablicowych jak CTE, tabel pochodnych, widoków, funkcji inline, funkcji tabelarycznych jak OPENROWSET i OPENXML. Można odnosić się w niej do tabel, tabel tymczasowych, zmiennych tabelarycznych itp. Warunkiem jest to aby klauzula USING zwracała wynik tablicowy. T-SQL rozszerza standard SQL dodając możliwość użycia klauzuli WHEN NOT MATCHED BY SOURCE. Dzięki tej klauzuli możemy zdefiniować akcję która będzie miała miejsce jeżeli wiersz docelowy istnieje a źródłowy nie. Dozwolonymi akcjami są UPDATE i DELETE. Przykładowo do ostatniego przykładu dodamy klauzulę która usuwa wiersz docelowy jeżeli nie ma go w źródle:

DECLARE @Orders AS TABLE
(
orderid INT NOT NULL PRIMARY KEY,
custid INT NOT NULL,
empid INT NOT NULL,
orderdate DATE NOT NULL
);
INSERT INTO @Orders(orderid, custid, empid, orderdate) VALUES
(2, 1, 3, '20120612'),
(3, 2, 2, '20120612'),
(4, 3, 5, '20120612');
MERGE INTO Sales.MyOrders AS TGT
USING @Orders AS SRC
ON SRC.orderid = TGT.orderid
WHEN MATCHED AND ( TGT.custid <> SRC.custid
OR TGT.empid <> SRC.empid
OR TGT.orderdate <> SRC.orderdate) THEN UPDATE
SET TGT.custid = SRC.custid,
TGT.empid = SRC.empid,
TGT.orderdate = SRC.orderdate
WHEN NOT MATCHED THEN INSERT
VALUES(SRC.orderid, SRC.custid, SRC.empid, SRC.orderdate)
WHEN NOT MATCHED BY SOURCE THEN
DELETE;

SELECT *
FROM Sales.MyOrders;

Wynik:

orderid custid empid orderdate ----------- ----------- ----------- ---------- 2 1 3 2012-06-12 3 2 2 2012-06-12 4 3 5 2012-06-12

Ćwiczenia

I. Użycie instrukcji MERGE

Uruchom poniższy kod do odtworzenia tabeli samle.MyOrders:

IF OBJECT_ID('Sales.MyOrders') IS NOT NULL DROP TABLE Sales.MyOrders;
IF OBJECT_ID('Sales.SeqOrderIDs') IS NOT NULL DROP SEQUENCE Sales.SeqOrderIDs;
CREATE SEQUENCE Sales.SeqOrderIDs AS INT
MINVALUE 1
CYCLE;
CREATE TABLE Sales.MyOrders
(
orderid INT NOT NULL
CONSTRAINT PK_MyOrders_orderid PRIMARY KEY
CONSTRAINT DFT_MyOrders_orderid
DEFAULT(NEXT VALUE FOR Sales.SeqOrderIDs),
custid INT NOT NULL
CONSTRAINT CHK_MyOrders_custid CHECK(custid > 0),
empid INT NOT NULL
CONSTRAINT CHK_MyOrders_empid CHECK(empid > 0),
orderdate DATE NOT NULL
);

Napisz instrukcje MERGE która scala dane z Sales.Orders do tabeli Sales.MyOrders. Sprawdź czy wiersze do siebie pasują na podstawie kolumny orderid. Jeżeli wiersze pasują zaktualizuj pozostałe kolumny. Jeżeli nie pasują to dodaj nowy wiersz. Nie aktualizuj wierszy gdy są identyczne.

MERGE INTO Sales.MyOrders AS TGT
USING Sales.Orders AS SRC
ON SRC.orderid = TGT.orderid
WHEN MATCHED AND ( TGT.custid <> SRC.custid
OR TGT.empid <> SRC.empid
OR TGT.orderdate <> SRC.orderdate) THEN UPDATE
SET TGT.custid = SRC.custid,
TGT.empid = SRC.empid,
TGT.orderdate = SRC.orderdate
WHEN NOT MATCHED THEN INSERT
VALUES(SRC.orderid, SRC.custid, SRC.empid, SRC.orderdate);

II. Zrozumienie roli klauzuli ON w instrukcji MERGE

Użyj poniższego kodu do wyczyszczenia tabeli Sales.MyOrders i wypełnienia jej danymi krajów innych niż Norwegia.

TRUNCATE TABLE Sales.MyOrders;
INSERT INTO Sales.MyOrders(orderid, custid, empid, orderdate)
SELECT orderid, custid, empid, orderdate
FROM Sales.Orders
WHERE shipcountry <> N'Norway';

Złącz zamówienia dla kraju Norwegia z tabeli Sales.Orders do tabeli Sales.MyOrders. Spróbuj zaimplementować instrukcję tak aby predykat shipcountry = N'Norway' była częścią klauzuli ON.
```
MERGE INTO Sales.MyOrders AS TGT
USING Sales.Orders AS SRC
ON SRC.orderid = TGT.orderid
AND shipcountry = N'Norway'
WHEN MATCHED AND ( TGT.custid <> SRC.custid
OR TGT.empid <> SRC.empid
OR TGT.orderdate <> SRC.orderdate) THEN UPDATE
SET TGT.custid = SRC.custid,
TGT.empid = SRC.empid,
TGT.orderdate = SRC.orderdate
WHEN NOT MATCHED THEN INSERT
VALUES(SRC.orderid, SRC.custid, SRC.empid, SRC.orderdate);
```
Wykonanie tego kodu spowoduje błąd:
Msg 2627, Level 14, State 1, Line 1 Violation of PRIMARY KEY constraint 'PK_MyOrders_orderid'. Cannot insert duplicate key in object 'Sales.MyOrders'. The duplicate key value is (10248). The statement has been terminated.
Powodem wystąpienia błędu jest to, że klauzula ON nie filtruje wierszy tylko porównuje wiersze źródłowe z docelowymi. Bazując na zapytaniu kraje inne niż Norwegia są uważane za nie pasujące i uruchamiają klauzulę WHEN NOT MATCHED która próbuje wstawić nowy wiersz i INSERT wywołuje błąd spowodowany naruszeniem klucza głównego.

Jako możliwe rozwiązanie filtrujmy wiersze dla Norwegii za pomocą CTE:

WITH SRC AS
(
SELECT *
FROM Sales.Orders
WHERE shipcountry = N'Norway'
)
MERGE INTO Sales.MyOrders AS TGT
USING Sales.Orders AS SRC
ON SRC.orderid = TGT.orderid
WHEN MATCHED AND ( TGT.custid <> SRC.custid
OR TGT.empid <> SRC.empid
OR TGT.orderdate <> SRC.orderdate) THEN UPDATE
SET TGT.custid = SRC.custid,
TGT.empid = SRC.empid,
TGT.orderdate = SRC.orderdate
WHEN NOT MATCHED THEN INSERT
VALUES(SRC.orderid, SRC.custid, SRC.empid, SRC.orderdate);

Podsumowanie

Instrukcja MERGE pozwala scalić dane z tabeli źródłowej do tabeli docelowej.
W klauzuli MERGE INTO wyznaczamy tabelę docelową. W klauzuli USING wyznaczamy tabelę źródłową. Klauzula USING jest zaprojektowana podobnie do klauzuli FROM zapytania SELECT. Można w niej użyć operatorów tabelarycznych, wyrażeń tabelarycznych, funkcji itp.
W klauzuli ON definiujemy predykat który sprawdza kiedy wiersz źródłowy pasuje do wiersza docelowego. Nie można w niej filtrować danych.
Definiujemy różne klauzule WHEN dla różnych akcji które mają być wykonane na tabeli docelowej w zależności od predykatu.