Pchełki SQL: MERGE

https://xpil.eu/TuI4J 📋

Operator MERGE nie jest niczym nowym. W świecie Microsoftu zaistniał sześć lat temu, w wersji 2008. W Oracle wprowadzono go w wersji 9i (a więc okolice 2004 roku). Standard ANSI wprowadził ten operator w roku 2003. Niektórzy z dostawców silników baz danych (na przykład MySQL) nie obsługują operatora MERGE do dziś, zamiast niego dopuszczają inne tricki, na przykład ON DUPLICATE KEY UPDATE ewentualnie REPLACE INTO.

Pokażę dziś najprostszy przykład użycia operatora MERGE, w składni zgodnej z MSSQL.

Zanim jednak przejdę do kodu, opowiem pokrótce cóż to za zwierz, ów MERGE.

Otóż MERGE to nic innego jak UPSERT, czyli kombinacja UPDATE i INSERT, z opcjonalnym DELETE, w jednym. Służy zasadniczo do synchronizowania danych pomiędzy tabelami. Jeżeli chcemy dodać do jakiejś tabeli T rekordy z innej tabeli S, których w T nie ma, "normalnie" zrobilibyśmy IF NOT EXISTS... ewentualnie LEFT OUTER JOIN... ON ... IS NULL, całość trzeba by pewnikiem obudować transakcją (bo nie skończyłoby się na jednym poleceniu, tylko na dwóch albo trzech osobnych: insert, update, delete) i tak dalej.

Operator MERGE daje nam dokładnie to samo, tylko w postaci pojedynczej operacji. Za cenę nauczenia się dodatkowego operatora oszczędzamy więc na wydajności, zwięzłości kodu - no i nie musimy opakowywać MERGE w transakcję.

No dobra. Tyle teorii, jak mawiał Wieszcz, zawsze nadchodzi taka chwila, kiedy trzeba albo zacząć srać, albo opuścić wychodek. Przejdźmy więc do konkretów.

Wyobraźmy sobie, że mamy dwie tabele: src1 oraz trg1. Próbujemy je ze sobą zsynchronizować, tzn. wstawić do tabeli trg1 wszystkie brakujące rekordy z tabeli src1, zaktualizować atrybuty w rekordach już istniejących oraz usunąć z trg1 rekordy, których nie ma w src1.

if object_id('dbo.src1') is not null drop table dbo.src1
create table src1(
	id int identity(1,1) not null primary key clustered,
	somedata varchar(100) not null,
	someotherdata varchar(100) null)

if object_id('dbo.trg1') is not null drop table dbo.trg1
create table trg1(
	id int identity(1,1) not null primary key clustered,
	data1 varchar(100) not null,
	data2 varchar(100) null,
	data3 int null)

insert src1 values
	('abc', 'abcd'),
	('bcd', 'bcde'),
	('cde','cdef')

insert trg1 values
	('abc', 'pqrs', 0),
	('def', 'qrst', 10),
	('ghi', 'rstu', null)

select 'BEFORE' ' ';
select * from src1;
select * from trg1;

merge into trg1
using src1
on trg1.data1 = src1.somedata
when matched then update set data2=src1.someotherdata
when not matched by target then insert (data1, data2) values(src1.somedata, src1.someotherdata)
when not matched by source then delete;

select 'AFTER' ' ';
select * from src1;
select * from trg1;

Szybkie omówienie powyższego kodu:

W wierszach 1-22 robimy grę wstępna, czyli tworzymy tabele src1 i trg1, i zapełniamy je przykładowymi danymi.

Wiersze 24-26 wyświetlają nam wszystkie dane przed wykonaniem operacji MERGE

Wiersze 28-33 to samo gęste, czyli operacja MERGE.

Wreszcie wiersze 35-37 wyświetlają sytuację końcową.

Efekt wykonania powyższego kodu jest następujący:

Jak widać, na początku w obydwu tabelach były po trzy rekordy, z czego jeden ('abc') w obydwu, natomiast dwa pozostałe ('bcd' i 'cde') tylko w źródłowej tabeli src1. Ponadto, docelowa tabela trg1 zawierała dwa rekordy, których nie było w tabeli źródłowej (a więc 'def' oraz 'ghi').

Po wykonaniu polecenia MERGE, w obydwu tabelach mamy pasujące zestawy rekordów: dodano rekordy 'bcd' i 'cde' do tabeli docelowej, usunięto z niej niepasujące rekordy 'def' i 'ghi', wreszcie zsynchronizowano atrybut data2 z atrybutem someotherdata.

Gdyby to samo chcieć zrobić bez używania operatora MERGE, trzeba by...

Nie. Za leniwy jestem.

[yop_poll id="51"]

https://xpil.eu/TuI4J 📋