Bevezetés a MERGE utasításba és az SQL Server adatmódosításába

A MERGE utasítással egy táblázatban változtatásokat lehet végrehajtani a portoktól párosított értékek alapján. Használható a beszúrási, frissítési és törlési műveletek egyetlen utasításba egyesítésére. Ebben a cikkben azt vizsgáljuk, hogy miként lehet használni a MERGE utasítást. Megbeszélünk néhány bevált gyakorlatot, korlátozásokat és összefoglalást számos példával.

Ez a cikksorozat ötödik cikke. Az elején elolvashatja a Bevezetés az SQL Server adatmódosítási nyilatkozatokba című részt.

A lecke összes példája a Microsoft SQL Server Management Studio és az AdventureWorks2012 adatbázisra épül. Ezeknek az ingyenes eszközöknek a használatát az Útmutató segítségével kezdhetem el. Az SQL Server használatának megkezdése

Mielőtt elkezdenénk

Bár ez a cikk az AdventureWorks adatbázist használja példáihoz, úgy döntöttem, hogy létrehozok több példatáblázat az adatbázisban történő használatra, hogy jobban szemléltesse az érintett fogalmakat. A futtatásához szükséges szkriptet itt találja. Ne feledje, hogy van egy külön szakasz, amely a MERGE-re vonatkozik.

Alapstruktúra

A MERGE utasítás az INSERT, a DELETE és az UPDATE műveleteket egy táblába egyesíti. Miután megértette a működését, látni fogja, hogy leegyszerűsíti az eljárást azzal, hogy mindhárom utasítást külön használja az adatok szinkronizálására.

Az alábbiakban az egyesítési utasítás általános formátuma látható.

MERGE targetTableUsing sourceTableON mergeConditionWHEN MATCHEDTHEN updateStatementWHEN NOT MATCHED BY TARGETTHEN insertStatementWHEN NOT MATCHED BY SOURCETHEN deleteStatement

Az egyesítési utasítás két tábla, a sourceTable és a targetTable használatával működik. A targetTable az a táblázat, amelyet a sourceTable-ben található adatok alapján kell módosítani.

A két táblázatot egy mergeCondition segítségével hasonlítják össze. . Ez a feltétel meghatározza, hogy a sourceTable sorai hogyan illeszkednek a targetTable-hez. Ha ismeri a BELSŐ CSATLAKOZÁSOKAT, úgy gondolhatja ezt, mint a sorok illesztésére használt csatlakozási feltételt.

Általában egyedi azonosítót, például egy elsődleges kulcsot illesztene össze. Ha a forrás tábla NewProduct és target ProductMaster volt, és mindkét ProductID elsődleges kulcsa, akkor a jó egyesítési feltétel a következő:

NewProduct.ProductID = ProductMaster.ProductID

Összevonási feltétel eredmény három állapot egyikében: PÁROSÍTOTT, NEM FELHASZNÁLT vagy NEM FELELŐDIK A FORRÁSBAN.

Feltételek egyesítése

Nézzük át, mit jelentenek a különféle feltételek:

Párosított – ezek olyan sorok, amelyek kielégítik a mérkőzés feltételét. A forrás- és a céltáblákban egyaránt közösek. Ábránkon zöld színnel vannak feltüntetve. Ha ezt a feltételt egy egyesülési nyilatkozatban használja, akkor; a legszívesebben a célsor oszlopainak frissítését a sourceTable oszlop értékeivel.

NEM MEGFELEL – Ez más néven NEM MEGFELEL A CÉL alatt; ezek olyan sorok a forrás táblából, amelyek nem egyeztek a céltábla egyetlen sorával sem. Ezeket a sorokat a fenti kék terület képviseli. A legtöbb esetben arra lehet következtetni, hogy a forrássorokat hozzá kell adni a targetTable-hez.

NEM EGYESÍTIK A FORRÁS – ezek a céltábla sorai, amelyeknek soha nem egyezett egy forrásrekord; ezek a sorok a narancssárga területen. Ha az a célja, hogy teljes mértékben szinkronizálja a targetTable adatokat a forrással, akkor ezt az egyezési feltételt használja a sorok törlésére.

Ha nehezen érti ennek működését, vegye fontolóra az egyesítés feltételét csatlakozási feltétel. A zöld szakasz sorai az egyesítési feltételnek megfelelő sorokat jelölik, a kék szakasz sorai azok, amelyek a SourceTable-ben találhatók, de a célban nem. A narancssárga szakasz sorai azok a sorok, amelyek csak a célpontban találhatók.

Adja meg ezeket a megfelelő forgatókönyveket, így könnyen hozzáadhatja, eltávolíthatja és frissítheti a tevékenységeket egyetlen utasításba, hogy szinkronizálhassa a két váltást. táblázatok.

Nézzünk meg egy példát.

EGYESÍTÉS Példa

Tegyük fel, hogy célunk az esqlProductSource minden módosításának szinkronizálása az esqlProductTarget-tal. Itt látható ennek a két táblának a diagramja:

Megjegyzés: A példa kedvéért lefuttattam azokat a szkripteket, amelyekről beszéltem a bevezetőben két tábla létrehozását és feltöltését: esqlProductSource és esqlProductTarget.

Mielőtt elkészítenénk a MERGE utasítást, nézzük meg, hogyan szinkronizálnánk a táblázatot az UPDATE, INSERT és DELETE utasításokkal a módosításhoz, hozzáadáshoz , és távolítsa el a sorokat a céltáblából.

Azt hiszem, ha egyszer meglátja, hogyan csináljuk ezt egyenként, akkor az egyetlen műveletbe egyesítésnek van értelme.

Az UPDATE használata a változások szinkronizálásához egyik tábláról a másikra

A céltábla frissítéséhez a termékforrás megváltozott értékeivel használhatunk egy UPDATE utasítást. Mivel a ProductID mindkét táblázat elsődleges kulcsa, ez lesz a legjobb választási párossági sorunk a táblák között.

Ha a forrás táblázat oszlopértékeit frissítenénk a céltáblában, akkor a következő frissítési utasítással megtehetnénk

UPDATE esqlProductTargetSET Name = S.Name, ProductNumber = S.ProductNumber, Color = S.ColorFROM esqlProductTarget T INNER JOIN esqlProductSource S ON S.ProductID = T.ProductID

Ez az utasítás frissíti az esqlProductTarget oszlopát az esqlProductSource megfelelő oszlopértékeivel az egyező productID-ekhez.

INSERT sorok találhatók az egyik táblázatban, de a többi nem.

Most nézzük meg, hogyan azonosítani tudja azokat a sorokat a forrás táblából, amelyeket be kell illesztenünk a termékcélba. Ehhez használhatunk subquery-t olyan sorok megkeresésére a forrás táblában, amelyek nincsenek a célban.

INSERT INTO esqlProductTarget (ProductID, Name, ProductNumber, Color)SELECT S.ProductID, S.Name, S.ProductNumber, S.ColorFROM esqlProductSource SWHERE NOT EXISTS (SELECT T.ProductID FROM esqlProductTarget T WHERE T.ProductID = S.ProductID)

Megjegyzés: Használhatnék külső csatlakozást is hogy ugyanezt tegyem. Ha érdekli, miért, olvassa el ezt a cikket.

Ez az utasítás új sort illeszt be az esqlProductTarget fájlba az esqlProductSource összes sorából, amely nem található meg az esqlProductTarget fájlban.

Eltávolítás Sorok

Ez az utolsó szinkronizálási tevékenység, amelyet meg kell tennünk, eltávolítja a céltáblázat azon sorait, amelyek nem szerepelnek az SQL Forrásban. Ahogy az insert utasítással tettük, használunk egy allekérdezést is. De ezúttal az esqlProductTarget sorokat fogjuk azonosítani, amelyek nem találhatók az esqlProductSource fájlban. Itt van a DELETE utasítás, amelyet használhatunk:

DELETE esqlProductTargetFROM esqlProductTarget TWHERE NOT EXISTS (SELECT S.ProductID FROM esqlProductSource S WHERE T.ProductID = S.ProductID)

Most, hogy látta, hogyan kell külön-külön elvégezni a különféle műveleteket, megnézheti, hogyan állnak össze a egyesítési utasítás.

Figyelje meg, hogy ott a nehéz emelés nagy részét az egyesítési feltétel és annak eredményei hajtják végre. Ahelyett, hogy ismételten be kellene állítanunk a mérkőzést, amint azt a törlési utasításban tettük, egyszer megtörténik.

Hasonlítsa össze újra az Insert utasítást a fenti egyesítési utasítással.

INSERT INTO esqlProductTarget (ProductID, Name, ProductNumber, Color)SELECT S.ProductID, S.Name, S.ProductNumber, S.ColorFROM esqlProductSource SWHERE NOT EXISTS (SELECT T.ProductID FROM esqlProductTarget T WHERE T.ProductID = S.ProductID)

Mivel a MERGE utasítás létrehozza a forrás- és céltáblát, valamint annak egyezését, minden piros színnel kódolt szín felesleges; ezért nem az egyesítés beszúrási részében.

VÁLTOZÁSOK EGYESÍTÉSE naplózása az OUTPUT használatával

Az OUTPUT záradékkal naplózhatja a változásokat. Ebben az esetben a speciális $ változó változó használható az egyesítési művelet naplózására. Ez a változó a három érték egyikét veszi fel: “INSERT”, “UPDATE” vagy “DELETE”.

A példánkat továbbra is használjuk, de ezúttal naplózza a változásokat és összefoglalja a változások.

Ha a fentiek friss mintaadatokon futnak, akkor a következő összefoglaló jön létre: