UPSERT, czyli UPDATE+INSERT w SQL Server
Wstaw gdy nie ma, zmodyfikuj gdy jest
Po przeczytaniu nagłówka jasne staje się, o czym dzisiaj będę prawił - o warunkowym wstawianiu rekordów do tabeli. Problem jest tak powszechny, że trochę zaskoczył mnie brak traktującego o nim wpisu. To się zaraz zmieni. Zagadnienie znane jest czasem pod nazwą UPSERT, powstałego ze złączenia słów UPDATE i INSERT - to dla mniej wtajemniczonych czytelników. Zanim przejdę do przykładów, przyjrzyjmy się wykorzystywanej w przykładach tabeli testowej:
(
ID int NOT NULL,--klucz
Value char(4), --wartość
CONSTRAINT PK_UPSERT_ID
PRIMARY KEY CLUSTERED(ID)
)
--Przykładowe wartości
INSERT INTO Upsert VALUES(1,'AAAA');
INSERT INTO Upsert VALUES(3,'CCCC');
To nasz poligon. Czas na kilka różnych rozwiązań.
Instrukcja warunkowa z przeszukaniem
Instrukcja warunkowa jest moim zdaniem najbardziej intuicyjna, przynajmniej dla tych, którzy zjedli zęby na proceduralnych językach programowania. Przyjrzyjmy się przykładowemu rozwiązaniu:
DECLARE @Value char(4)='BBBB';
--Sprawdź istnienie rekordu:
--Jeżeli istnieje, wykonaj UPDATE
--Jeżeli nie istnieje, wykonaj INSERT
IF (EXISTS(SELECT * FROM Upsert WHERE ID=@ID))
UPDATE Upsert SET Value=@Value WHERE ID=@ID;
ELSE
INSERT INTO Upsert VALUES(@ID, @Value);
Pierwsze wykonanie skryptu wstawi nowy rekord z wartością BBBB i identyfikatorem 2, a każde kolejne będzie dokonywało modyfikacji tego rekordu (o ile modyfikacją można nazwać zmianę na to samo).
Operacja zawsze będzie wykonywana w postaci dwóch instrukcji - najpierw SELECT, a potem, w zależności od istnienia bądź nieobecności rekordu, UPDATE lub INSERT. Operacja warunkowa pociąga za sobą konieczność przeszukania tabeli. Dobrze, jeżeli mamy odpowiedni indeks, który przyspieszy nam taką operację. Nie zmienia to faktu, że system musi wykonywać dwie operacje. Wiadomo nie od dziś, że dwie nieatomowe instrukcje mogą na nas sprowadzić problemy związane ze współbieżnością - jeżeli po wykonaniu instrukcji SELECT uruchomiony zostanie inny wątek, i ten inny wątek wstawi przetwarzany przez nas rekord... BUM!
Nie jest to jednak jedyny sposób, z którym udało mi się spotkać. Popatrzmy na inny.
UPDATE, a jak nie to INSERT
Pokazane poniżej rozwiązanie jest próbą ograniczenia wykonywanych operacji i trzeba przyznać - bardzo pomysłową. Problem polega na tym, że ograniczenie następuje tylko wtedy, gdy rekord już jest. Dlaczego? Wyjaśni się po obejrzeniu przykładu:
DECLARE @Value char(4)='BBBB';
--Wykonaj UPDATE:
--Jeżeli się udało - był i został zamodyfikowany
--Jeżeli się nie udało, wstaw nowy rekord
UPDATE Upsert SET Value=@Value WHERE ID=@ID;
IF @@ROWCOUNT=0
INSERT INTO Upsert VALUES(@ID, @Value);
Działanie jest proste i zostało wyjaśnione w komentarzu: jeżeli instrukcja UPDATE coś zmodyfikowała - nic nie rób. Rekord był, został zmodyfikowany i tego oczekiwaliśmy. Jeżeli instrukcja UPDATE niczego nie zmodyfikowała, to znaczy, że takiego rekordu nie było. A skoro nie było (@@ROWCOUNT=0), to należy go wstawić. W tym drugim przypadku wykonywane są dwie instrukcje. Przypomnę, dwie niezależne instrukcje. Sytuację może ratować odpowiednie blokowanie tabel i transakcja, ale jest to temat zbyt obszerny i będzie omówiony oddzielnie. Byłby omówiony tutaj, gdyby nie było trzeciego rozwiązania.
Złączenie danych - MERGE
W standardzie SQL:2003 pojawiła się nowa instrukcja MERGE, która, teoretycznie, powinna służyć do łączenia danych z dwóch źródeł. To nic, że jednym źródłem będzie pojedynczy rekord.
Zwykle jest tak, że standard idzie swoją ścieżką, a twórcy silników bazodanowych swoją. Microsoft wypuszczająć SQL Server 2005 nie zdążył z implementacją instrukcji MERGE, ale udało mu się to w SQL Server 2008. Wniosek: przykład wymaga SQL Server w wersji 2008 lub wyższej. Standard jest jednak nie tylko po to, aby programiści i architekci mieli pracę - dzięki standardom ta sama instrukcja może być zastosowana w niezmienionej postaci w bazach danych innych niż SQL ServerO ile nie stosuje się innych, zależnych od silnika bazy danych instrukcji. W tym przypadku problemem może być sposób przekazywania parametrów i konstruktor wartości tablicowych.. Instrukcja MERGE jest, i mówię tu z wielką pewnością, intrukcją trudną. Łączy ona w sobie JOIN, INSERT, UPDATE, DELETE i kilka innych konstrukcji, dlatego jej omówienie pozostawiam sobie na inny termin. My przyjrzyjmy się gotowemu rozwiązaniu problemu postawionego w temacie wpisu pokazanemu na poniższym listingu:
DECLARE @Value char(4)='BBBB';
--Atomowa instrukcja MERGE
MERGE Upsert AS Dest
USING (VALUES(@ID,@Value)) AS Src(SrcID, SrcValue)
ON ID=SrcID
WHEN MATCHED THEN
UPDATE SET Value=SrcValue
WHEN NOT MATCHED THEN
INSERT VALUES(SrcID,SrcValue);
Instrukcja MERGE jest z definicji, jako pojedyncza instrukcja, operacją atomową. Nie wymaga żadnych synchronizacji i jawnych transakcji. O ile dysponujemy wersją SQL Server 2008 lub wyższą, będzie to też najwydajniejsze rozwiązanie. Prawdę mówiąc, gdyby nie konieczność wspierania starszych wersji baz danych, mógłbym umieścić tę metodę jako jedyną i zalecaną. Z moich obserwacji wynika jednak, że instrukcja MERGE jest mało znana. I nie mówię tutaj o samej składni, ale o fakcie jej istnienia. Grupę operacji na danych zwykle zamykają cztery słowa kluczowe: SELECT, INSERT, UPDATE i DELETE. Do nich dołączyło MERGE, ale traktowane jest trochę jak brzydkie kaczątko, jak bękart, jak trędowaty. A szkoda, bo lista zastosowań MERGE jest ogromna.
We wszystkich przykładach zastosowałem parametry, aby ułatwić testy i próby z różnymi zestawami danych wejściowych.
Kategoria:SQL ServerMerge
Komentarze: