Příkaz SQL MERGE byl uveden již v roce 2008 a je to pokročilý způsob jak porovnat 2 tabulky (Source a Destination). Porovnáváme záznamy a na základě shody (nebo neshody) provádíme UPDATE (změnu záznamů), INSERT (vkádání záznamů) nebo DELETE (mazání) podle toho, jak porovnání záznamů v tabulkách dopadne. Využijeme zejména při synchronizaci dat mezi objekty/systémy nebo při ETL procesech v rámci datového skladu. V tomto článku se podíváme na syntaxi, příklad a krátce zhrneme výkon merge oproti insertu/updatu.

Obecně MERGE nedoporučuji používat pro komplexní prostředí. Pokoušel jsem jej v minulosti nasadit nad datový sklad, nicméně v některých scénářích není vhodný – skripty běžely příliš dlouho, timeoutovaly nebo se projevovaly jiné bugy. Kompletní list stále nevyřešených bugu je možné najít třeba na mssqltips 1

Syntaxe MERGE nad SQL Server

Syntaxe merge by se dala logicky rozdělit řekněme na 4 části 2

  1. V první části definujeme naši cílovou a zdrojovou tabulku (ze které synchronizujeme) na základě nějaké podmínky v “ON” (podobně jako u JOINu řekneme jak tabulky propojíme)
  2. Následně říkáme co se stane když se záznamy v obou tabulkách najdou (WHEN MATCHED) – typicky budeme chtít udělat update (synchronizaci)
  3. Co se stane když v cílové tabulce záznam není – uděláme INSERT (na zdroji došlo k vytvoření nového záznamu)
  4. Co se stane když není záznam ve zdrojové tabulce – uděláme DELETE (na zdroji se data chybí – smazaly se)
MERGE CilovaTabulka AS TARGET
USING ZdrojovaTabulka AS SOURCE
                  ON
WHEN MATCHED THEN
WHEN NOT MATCHED BY TARGET THEN
WHEN NOT MATCHED BY SOURCE THEN ;

 

Jak by vypadal konkrétní skript pro konkrétní situaci se můžeme podívat dále. To, co tu vidíme je něco neuvěřitelně přehledného, nicméně v určitých scénářích to může být na druhou stranu “doplaceno” výkonem – viz dále.

Příklad použití příkazu

Představme si 2 tabulky a dejme tomu, že je budeme mezi sebou synchronizovat:

  • dbo.PracovniPozice (DESTINATION) – Cílová tabulka, která se nachází v datovém skladu
  • dbo.PracovniPozice_ZdrojovySystem (SOURCE) –  Tabulka ve zdrojovém systému ze které budeme synchronizovat

Synchronizované pole bude pozice zamestnanců – “ZamestnanecPozice”. Syntaxe sql MERGE je velmi intuitivní a bude obsahovat 3 větve:

  • Pokud záznam existuje na zdroji i cilové destinaci, provádíme UPDATE (WHEN MATCHED)
  • Pokud záznam v cílové tabulce neexistuje, provádíme INSERT (NOT MATCHED)
  • Pokud záznam neexistuje ve zrojové tabulce a cílové ano, provádíme DELETE (NOT MATCHED BY SOURCE)
MERGE dbo.PracovniPozice AS TARGET
USING  dbo.PracovniPozice_ZdrojovySystem AS SOURCE
                  ON TARGET.ID_Pozice = SOURCE.ID_Pozice
WHEN MATCHED AND 1=1 THEN 
                  UPDATE SET TARGET.ZamestnanecPozice=SOURCE.ZamestnanecPozice
WHEN NOT MATCHED BY TARGET THEN
                  INSERT (ID_Pozice, ZamestnanecPozice)
                  VALUES (ID_Pozice, ZamestnanecPozice)
WHEN NOT MATCHED BY SOURCE THEN
                 DELETE;

 

Poznámka: Pokud nevíte k čemu jsem to sekce “WHEN MATCHED” psal AND 1=1, tak jsem tím chtěl naznačit, že můžete v MATCHED částech definovat podmínky. Pokud bychom chtěli synchronizovat zdrojovou tabulku a cílovou tabulku přes více polí, tak bychom museli UPDATE část rozšířit.

Výkon SQL příkazu MERGE

Psal jsem, že MERGE je krásně přehledný – nezabírá v kódu hodně místa. Jedním vrzem na pár řádcích SQL skriptu dělá kompletní synchronizaci 2 tabulek. Proto se často MERGE někdy používá v datových skladech při při identifikaci inkrementu ze zdroje anebo při počítání faktových a dimenzních tabulek. Nicméně jak jsem psal na začátku – po důkladném otestování a za cenu mírného zhoršení výkomu v porovnání s INSERT/UPDATE samostatně. V datových skladech je pochopitelně optimalizace SQL dotazů důležitým tématem, ale pokud nám dotaz běží o vteřinu déle než by musel nemusí být zase tak kritická věc jako u aplikací.

S použitím nad aplikacemi nemám zkušenosti. Dovedu si představit použití i tam, nicméně je potřeba vzít v potaz jednu věc. Tím, že merge dělá 3 věci naráz, kdy často všechny MATCHED testy nepotřebujeme dělat, stavá se pro určité scénáře pomalejším než bychom chtěli. To je pro aplikace kritická věc a je potřeba to otestovat konkrétně pro náš scénář.

Pěkný článek o výkonu tohoto příkazu před časem sepsal Dwain Camps z SQL Server Central 3. V kostce testoval source/destination tabulku a u 500 tis záznamu došlo k insertu/ 500tis k updatu a následně ještě 1 scénář. To jsou tedy spíše častěji scénáře pro datové sklady nebo velké aplikace. Výsledek obou scénářů se lišil s tím, že MERGE je v obou více či méně pomalejší.

5/5 - (3 votes)

Použité zdroje
  1. Aaron Bertrand – MSSQLTips, Use Caution with SQL Server’s MERGE Statement [on-line]. [cit. 2017-04-30]. Dostupné z WWW:  https://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/
  2. Microsoft dokumentace, MERGE (Transact-SQL) [on-line]. [cit. 2017-04-30]. Dostupné z WWW: https://learn.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=sql-server-ver16
  3. Dwain Camps – SQL Server Central, Performance of the SQL MERGE vs. INSERT/UPDATE [on-line]. [cit. 2017-04-30]. Dostupné z WWW:  https://www.sqlservercentral.com/articles/performance-of-the-sql-merge-vs-insertupdate

Ing. Jan Zedníček - Data Engineer & Controlling

Jmenuji se Honza Zedníček a působím jako freelancer. Pracoval jsem dříve také jako BI developer, finanční controller a analytik. Vše pro společnosti z oblasti IT, bankovnictví, consultingu a výroby. Po práci si rád zahraju tenis, volejbal, šachy, zajdu do posilovny a občas neúspěšně odpálím pár balónků v golfu 🏌️

Již cca 10 let zapisuji na tento web různé návody určené zejména odborné veřejnosti, studentům a zájemcům o informace z oblastí Business intelligence, korporátních financí a reportingu.

🔥 Přihlašte se do naší Excel facebook skupiny (2.4k+ členů), kde si pomáháme Excel CZ/SK diskuse »

Leave a Reply

Your email address will not be published. Required fields are marked *