ÖSSZEOLVADÁS (Transact-SQL)

Vonatkozik a következőkre:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics (dedicated SQL pool only)SQL adatbázis a Microsoft Fabric-benRaktár a Microsoft Fabric-ben

Az MERGE utasítás beszúrási, frissítési vagy törlési műveleteket futtat egy céltáblán egy forrástáblával való illesztés eredményeiből. Szinkronizálhat például két táblát úgy, hogy sorokat szúr be, frissít vagy töröl az egyik táblában a másik táblában található különbségek alapján.

Ez a cikk különböző szintaxisokat, argumentumokat, megjegyzéseket, engedélyeket és példákat tartalmaz a kiválasztott termékverzió alapján. Válassza ki a kívánt termékverziót a verzió legördülő listájából.

Transact-SQL szintaxis konvenciók

Syntax

Az SQL Server és az Azure SQL Database szintaxisa:

[ WITH <common_table_expression> [,...n] ]
MERGE
    [ TOP ( expression ) [ PERCENT ] ]
    [ INTO ] <target_table> [ WITH ( <merge_hint> ) ] [ [ AS ] table_alias ]
    USING <table_source> [ [ AS ] table_alias ]
    ON <merge_search_condition>
    [ WHEN MATCHED [ AND <clause_search_condition> ]
        THEN <merge_matched> ] [ ...n ]
    [ WHEN NOT MATCHED [ BY TARGET ] [ AND <clause_search_condition> ]
        THEN <merge_not_matched> ]
    [ WHEN NOT MATCHED BY SOURCE [ AND <clause_search_condition> ]
        THEN <merge_matched> ] [ ...n ]
    [ <output_clause> ]
    [ OPTION ( <query_hint> [ ,...n ] ) ]
;

<target_table> ::=
{
    [ database_name . schema_name . | schema_name . ] [ [ AS ] target_table ]
    | @variable [ [ AS ] target_table ]
    | common_table_expression_name [ [ AS ] target_table ]
}

<merge_hint>::=
{
    { [ <table_hint_limited> [ ,...n ] ]
    [ [ , ] { INDEX ( index_val [ ,...n ] ) | INDEX = index_val }]
    }
}

<merge_search_condition> ::=
    <search_condition>

<merge_matched>::=
    { UPDATE SET <set_clause> | DELETE }

<merge_not_matched>::=
{
    INSERT [ ( column_list ) ]
        { VALUES ( values_list )
        | DEFAULT VALUES }
}

<clause_search_condition> ::=
    <search_condition>

Arguments

A COMMON_TABLE_EXPRESSION <>

Az utasítás hatókörében MERGE definiált ideiglenes elnevezett eredményhalmazt vagy nézetet (más néven közös táblakifejezést) adja meg. Az eredményhalmaz egy egyszerű lekérdezésből származik, és az MERGE utasítás hivatkozik gombra. További információ: WITH common_table_expression (Transact-SQL).

TOP ( kifejezés ) [ PERCENT ]

Az érintett sorok számát vagy százalékos arányát adja meg. kifejezés lehet szám vagy a sorok százalékos aránya. A kifejezésben TOP hivatkozott sorok nincsenek rendezve semmilyen sorrendben. További információ: TOP (Transact-SQL).

A TOP záradék a teljes forrástábla és a céltábla teljes illesztése, valamint a beszúrási, frissítési vagy törlési művelethez nem megfelelő illesztett sorok eltávolítása után érvényes. A TOP záradék tovább csökkenti az összekapcsolt sorok számát a megadott értékre. Ezek a műveletek (beszúrás, frissítés vagy törlés) rendezetlen módon vonatkoznak a többi illesztett sorra. Vagyis nincs olyan sorrend, amelyben a sorok el vannak osztva a WHEN záradékokban meghatározott műveletek között. A (10) megadása TOP például 10 sort érint. Ezen sorok közül 7 frissíthető és 3 beszúrható, illetve 1 törölhető, 5 frissítve és 4 beszúrva stb.

A forrástábla szűrői nélkül az MERGE utasítás táblavizsgálatot vagy fürtözött indexvizsgálatot végezhet a forrástáblán, valamint a céltábla táblavizsgálatát vagy fürtözött indexvizsgálatát. Ezért az I/O teljesítménye néha akkor is hatással van, ha a TOP záradékkal több köteg létrehozásával módosít egy nagy táblát. Ebben a forgatókönyvben fontos biztosítani, hogy minden egymást követő köteg új sorokat célozhasson meg.

database_name

Annak az adatbázisnak a neve, amelyben a target_table található.

schema_name

Annak a sémának a neve, amelyhez target_table tartozik.

target_table

Az a tábla vagy nézet, amely alapján <table_source> az adatsorok egyeznek <clause_search_condition>. target_table az utasítás záradékai által WHEN megadott beszúrási, frissítési vagy törlési MERGE műveletek célja.

Ha target_table nézet, az ellene irányuló műveleteknek meg kell felelniük a nézetek frissítésének feltételeinek. További információ: Adatok módosítása nézeten keresztül.

target_table nem lehet távoli tábla. target_table nem definiálhatók rajta szabályok. target_table nem lehet memóriaoptimalizált tábla.

A tippek megadhatóak <merge_hint>.

[ MINT ] table_alias

Egy másik név, amely a target_table táblára hivatkozik.

TABLE_SOURCE HASZNÁLATA <>

A target_table adatsoraival egyező adatforrást adja meg az alapján <merge_search_condition>. Ennek az egyezésnek az eredménye diktálja az utasítás záradékai WHEN által MERGE végrehajtandó műveleteket. <table_source> Lehet távoli tábla vagy távoli táblákhoz hozzáférő származtatott tábla.

<table_source> olyan származtatott tábla lehet, amely a Transact-SQL táblaérték-konstruktort használja egy tábla több sor megadásával történő létrehozásához.

[ MINT ] table_alias

Egy másik név, amely a table_source táblára hivatkozik.

A záradék szintaxisával és argumentumával kapcsolatos további információkért lásd: FROM (Transact-SQL).

A merge_search_condition <>

Meghatározza azokat a feltételeket, amelyeken <table_source>a target_table összekapcsolja az illesztéseket annak meghatározásához, hogy hol egyeznek.

Caution

Fontos, hogy csak a céltáblából származó oszlopokat adja meg egyezés céljából. Vagyis adja meg a céltábla azon oszlopait, amelyek a forrástábla megfelelő oszlopával vannak összehasonlítva. Ne próbáljon javítani a lekérdezési teljesítményen a záradék céltáblájának ON sorainak szűrésével, például a megadásával AND NOT target_table.column_x = value. Ez váratlan és helytelen eredményeket adhat vissza.

HA MEGFELEL, AKKOR <merge_matched>

Megadja, hogy a *target_table összes olyan sora, amely megfelel a visszaadott <table_source> ON <merge_search_condition>soroknak, és megfelel a további keresési feltételeknek, a záradéknak megfelelően <merge_matched> frissüljön vagy törölve legyen.

Az MERGE utasítás legfeljebb két WHEN MATCHED záradékkal rendelkezhet. Ha két záradék van megadva, az első záradékot záradékkal kell kiegészíteni AND<search_condition> . Egy adott sor esetében a második WHEN MATCHED záradék csak akkor lesz alkalmazva, ha az első nem. Ha két WHEN MATCHED záradék van, az egyiknek meg kell adnia egy UPDATE műveletet, a másiknak pedig egy DELETE műveletet. Ha UPDATE a <merge_matched> záradék meg van adva, és egynél több sor <table_source> felel meg egy sornak target_table alapján <merge_search_condition>, az SQL Server hibát ad vissza. Az MERGE utasítás nem tudja többször frissíteni ugyanazt a sort, vagy frissíteni és törölni ugyanazt a sort.

HA NEM EGYEZIK [CÉL] AKKOR <merge_not_matched>

Megadja, hogy a rendszer minden olyan sorhoz beszúrjon egy sort target_table, amely <table_source> ON <merge_search_condition> nem egyezik meg a target_table egy sorával, de ha van ilyen, egy további keresési feltételnek is megfelel. A beszúrni kívánt értékeket a <merge_not_matched> záradék határozza meg. Az MERGE utasításnak csak egy WHEN NOT MATCHED [ BY TARGET ] záradéka lehet.

HA NEM EGYEZIK A FORRÁS, AKKOR <MERGE_MATCHED>

Megadja, hogy a *target_table összes olyan sora, amely nem egyezik a visszaadott <table_source> ON <merge_search_condition>sorokkal, és amelyek megfelelnek a további keresési feltételeknek, a záradéknak megfelelően <merge_matched> frissülnek vagy törlődnek.

Az MERGE utasítás legfeljebb két WHEN NOT MATCHED BY SOURCE záradékkal rendelkezhet. Ha két záradék van megadva, akkor az első záradékhoz záradékot AND<clause_search_condition> kell mellékelni. Egy adott sor esetében a második WHEN NOT MATCHED BY SOURCE záradék csak akkor lesz alkalmazva, ha az első nem. Ha két WHEN NOT MATCHED BY SOURCE záradék van, akkor meg kell adnia egy UPDATE műveletet, egy pedig meg kell adnia egy DELETE műveletet. Csak a céltáblából származó oszlopokra lehet hivatkozni a következőben <clause_search_condition>: .

Ha a rendszer nem ad vissza <table_source>sorokat, a forrástábla oszlopai nem érhetők el. Ha a záradékban megadott frissítési vagy törlési <merge_matched> művelet a forrástáblában lévő oszlopokra hivatkozik, a rendszer a 207-s hibát (érvénytelen oszlopnevet) adja vissza. A záradék WHEN NOT MATCHED BY SOURCE THEN UPDATE SET TargetTable.Col1 = SourceTable.Col1 például az utasítás meghiúsulását okozhatja, mert Col1 a forrástáblában nem érhető el.

ÉS <clause_search_condition>

Érvényes keresési feltételt ad meg. További információ: Keresési feltétel (Transact-SQL).

<table_hint_limited>

Egy vagy több táblázatra vonatkozó tippet ad meg a céltáblára az utasítás által végrehajtott beszúrási, frissítési vagy törlési MERGE műveletek mindegyikéhez. A WITH kulcsszó és a zárójelek megadása kötelező.

NOLOCK és READUNCOMMITTED nem engedélyezettek. A táblázatos tippekről további információt a Table hints (Transact-SQL) című témakörben talál.

TABLOCK Az utasítás céljának INSERT megfelelő táblára vonatkozó tipp megadása ugyanolyan hatással van, mint a TABLOCKX tipp megadására. Az asztalra kizárólagos zárolás kerül. Ha a FORCESEEK meg van adva, az a forrástáblához csatlakoztatott céltábla implicit példányára vonatkozik.

Caution

A megadásával READPASTWHEN NOT MATCHED [ BY TARGET ] THEN INSERT olyan műveletek következhetnek beINSERT, amelyek megsértik UNIQUE a korlátozásokat.

INDEX ( index_val [ ,... n ] )

Megadja a céltábla egy vagy több indexének nevét vagy azonosítóját, amely implicit illesztéseket végez a forrástáblával. További információkért lásd: Tábla-javallatok (Transact-SQL).

<output_clause>

Egy sort ad vissza a target_table minden olyan sorához, amely adott sorrendben frissül, beszúrt vagy törölve van. $action a kimeneti záradékban adható meg. $action egy nvarchar(10) típusú oszlop, amely minden sor három értékének egyikét adja vissza: INSERT, UPDATEvagy DELETE, az adott sorban végrehajtott műveletnek megfelelően. A OUTPUT záradék az ajánlott módszer egy adott sor által érintett sorok lekérdezésére vagy megszámlálására MERGE. A záradék argumentumairól és viselkedéséről további információt az OUTPUT záradékban (Transact-SQL) talál.

OPTION ( <query_hint> [ ,... n ] )

Meghatározza, hogy az optimalizáló tippek segítségével szabja testre az adatbázismotor az utasítást. További információ: Lekérdezési tippek (Transact-SQL).

<merge_matched>

Megadja az összes olyan target_table sorra alkalmazott frissítési vagy törlési műveletet, amely nem egyezik a visszaadott <table_source> ON <merge_search_condition>sorokkal, és amelyek megfelelnek a további keresési feltételeknek.

FRISSÍTÉS set_clause <>

Megadja a céltáblában frissíteni kívánt oszlop- vagy változónevek listáját, valamint azokat az értékeket, amelyekkel frissíteni szeretné őket.

A záradék argumentumairól további információt az UPDATE (Transact-SQL) című témakörben talál. Ha egy változót egy oszlop értékére állít be, az nem támogatott.

DELETE

Megadja, hogy a target_table sorainak megfelelő sorok törlődnek.

<merge_not_matched>

Megadja a céltáblába beszúrni kívánt értékeket.

( column_list )

A céltábla egy vagy több oszlopának listája, amelyben adatokat szeretne beszúrni. Az oszlopokat egyrészes névként kell megadni, különben az MERGE utasítás meghiúsul. column_list zárójelek közé kell tenni, és vesszővel kell elválasztani.

ÉRTÉKEK ( values_list )

Olyan állandók, változók vagy kifejezések vesszővel tagolt listája, amelyek értékeket adnak vissza a céltáblába való beszúráshoz. A kifejezések nem tartalmazhatnak utasítást EXECUTE .

ALAPÉRTELMEZETT ÉRTÉKEK

Kényszeríti a beszúrt sort, hogy tartalmazza az egyes oszlopokhoz definiált alapértelmezett értékeket.

A záradékról további információt az INSERT (Transact-SQL) című témakörben talál.

<search_condition>

Megadja a megadható <merge_search_condition> keresési feltételeket vagy <clause_search_condition>a . A záradék argumentumairól további információt a Keresési feltétel (Transact-SQL) című témakörben talál.

<gráfkeresési minta>

A gráfegyezés mintáját adja meg. A záradék argumentumairól további információt a HOL.VAN (Transact-SQL) című témakörben talál.

Remarks

Az utasításhoz MERGE leírt feltételes viselkedés akkor működik a legjobban, ha a két tábla egyező jellemzők összetett keverékével rendelkezik. Például beszúrhat egy sort, ha az nem létezik, vagy frissíthet egy sort, ha megegyezik. Ha egyszerűen frissít egy táblát egy másik tábla sorai alapján, javítsa a teljesítményt és a méretezhetőséget a , INSERTés UPDATE utasításokkalDELETE. Például:

INSERT tbl_A (col, col2)
SELECT col, col2
FROM tbl_B
WHERE NOT EXISTS (SELECT col FROM tbl_A A2 WHERE A2.col = tbl_B.col);

A három MATCHED záradék közül legalább egyet meg kell adni, de bármilyen sorrendben megadhatók. Egy változó nem frissíthető többször ugyanabban MATCHED a záradékban.

A céltáblán az utasítás által MERGE megadott beszúrási, frissítési vagy törlési műveletet a rajta meghatározott korlátozások korlátozzák, beleértve az esetleges kaszkádolt hivatkozási integritási korlátozásokat is. Ha IGNORE_DUP_KEY a céltábla bármely egyedi indexéhez tartozik ON , MERGE figyelmen kívül hagyja ezt a beállítást.

Az MERGE utasításhoz pontosvesszőre (;) van szükség utasítás-kifejezésként. Az 10713-ás hiba akkor jelenik meg, ha egy MERGE utasítás a kifejezés nélkül fut.

A MERGE után használva a beszúrt, frissített és törölt sorok teljes számát adja vissza az ügyfélnek.

MERGE teljes mértékben fenntartott kulcsszó, ha az adatbázis kompatibilitási 100 szintje értéke vagy magasabb. Az MERGE utasítás elérhető mind az 90100 adatbázis-kompatibilitási szinteken, mind pedig az adatbázis-kompatibilitási szinteken. A kulcsszó azonban nem foglalható le teljesen, ha az adatbázis kompatibilitási szintje a következőre 90van állítva.

Caution

Ne használja az utasítást az MERGEüzenetsoros frissítési replikáció használatakor. A MERGE várólistán lévő frissítési eseményindító nem kompatibilis. Cserélje le az MERGE utasítást egy és INSERT egy UPDATE utasításra.

Trigger implementálása

Az utasításban megadott minden beszúrási, frissítési vagy törlési művelet esetében az MERGE SQL Server elindítja a céltáblán definiált megfelelő AFTER eseményindítókat, de nem garantálja, hogy melyik művelet aktiválja először vagy utoljára az eseményindítókat. Az ugyanahhoz a művelethez definiált triggerek betartják a megadott sorrendet. Az eseményindítók kilövési sorrendjének beállításáról további információt az Első és az Utolsó eseményindító megadása című témakörben talál.

Ha a céltábla rendelkezik egy utasítással INSTEAD végzett beszúrási, frissítési vagy törlési művelethez definiált MERGE OF eseményindítóval, akkor az utasításban megadott összes művelethez engedélyezni INSTEAD kell az MERGE OF eseményindítót.

Ha bármelyik INSTEAD OF UPDATE vagy INSTEAD OF DELETE eseményindítót definiálja target_table, a frissítési vagy törlési műveletek nem futnak. Ehelyett az eseményindítók kigyulladnak, a beszúrt és törölt táblák pedig ennek megfelelően lesznek feltöltve.

Ha bármelyik INSTEAD OF-eseményindító INSERT definiálva van target_table, a beszúrási művelet nem lesz végrehajtva. Ehelyett a tábla ennek megfelelően töltődik fel.

Note

A különálló INSERT, UPDATEés DELETE az utasításoktól eltérően az eseményindítón belül tükröződő @@ROWCOUNT sorok száma magasabb lehet. Az @@ROWCOUNT eseményindítók AFTER belső része (az adatmódosítási utasításoktól függetlenül, amelyeket az eseményindító rögzít) az érintett MERGEsorok teljes számát tükrözi. Ha például egy MERGE utasítás beszúr egy sort, frissít egy sort, és töröl egy sort, @@ROWCOUNT akkor az eseményindító AFTER három lesz, még akkor is, ha az eseményindító csak utasításokhoz INSERT van deklarálva.

Permissions

Engedélyre van szükség SELECT a forrástáblában és INSERTa UPDATEcéltáblán, vagy DELETE engedélyeket igényel. További információ: SELECT (Transact-SQL), INSERT (Transact-SQL), UPDATE (Transact-SQL), és DELETE (Transact-SQL) cikkekben.

Ajánlott indexelési eljárások

Az utasítás használatával MERGE az egyes DML-utasításokat egyetlen utasításra cserélheti. Ez javíthatja a lekérdezési teljesítményt, mivel a műveletek egyetlen utasításban vannak végrehajtva, így minimalizálva a forrás- és céltáblákban lévő adatok feldolgozásának hányszorosát. A teljesítménynövekedés azonban attól függ, hogy megfelelő indexek, illesztések és egyéb szempontok vannak-e érvényben.

Az utasítás teljesítményének MERGE javítása érdekében az alábbi indexelési irányelveket javasoljuk:

• Indexek létrehozása a forrás és a cél közötti illesztés megkönnyítéséhez MERGE:
  • Hozzon létre egy indexet a forrástábla illesztésoszlopaihoz, amely a céltáblához tartozó illesztéslogikát lefedő kulcsokkal rendelkezik. Ennek lehetőség szerint egyedinek kell lennie.
  • Emellett hozzon létre egy indexet a céltábla illesztőoszlopaihoz. Ennek lehetőleg egyedi fürtözött indexnek kell lennie.
  • Ez a két index biztosítja, hogy a táblák adatai rendezve legyenek, az egyediség pedig segíti az összehasonlítás teljesítményét. A lekérdezés teljesítménye javul, mert a lekérdezésoptimalizálónak nem kell további ellenőrzési feldolgozást végeznie az ismétlődő sorok megkereséséhez és frissítéséhez, és nincs szükség további rendezési műveletekre.
• Kerülje azokat a táblákat, amelyek bármilyen típusú oszlopcentrikus indexet tartalmaznak az utasítások céljaként MERGE . Az UPDAT-ekhez hasonlóan az oszlopcentrikus indexekkel is jobb teljesítményt érhet el egy szakaszos sortártábla frissítésével, majd egy kötegelt DELETE és INSERTegy UPDATE vagy MERGE.

A MERGE egyidejűségi szempontjai

A zárolás szempontjából különbözik a különálló, MERGE egymást követő INSERTés UPDATEDELETE utasításoktól. MERGE továbbra is végrehajtja INSERT, UPDATEés DELETE műveleteket, azonban különböző zárolási mechanizmusok használatával. Hatékonyabb lehet különálló , INSERTés UPDATE utasításokat írni DELETEbizonyos alkalmazásigényekhez. Nagy léptékben összetett egyidejűségi problémákat eredményezhet, MERGE vagy speciális hibaelhárítást igényelhet. Ezért tervezze meg, hogy alaposan tesztelje az összes MERGE utasítást, mielőtt üzembe helyeznénk az éles környezetben.

MERGE az utasítások az alábbi forgatókönyvekben (de nem kizárólag) a különálló INSERT, UPDATEés DELETE a műveletek megfelelő helyettesítői:

• A nagy sorszámú ETL-műveletek végrehajtása olyan időszakban történik, amikor más egyidejű műveletek nem* várhatók. Ha nagy egyidejűség várható, a különálló INSERT, UPDATEés DELETE a logika jobban teljesíthet, kevesebb blokkolással, mint egy MERGE utasítás.
• A kis sorszámokat és tranzakciókat tartalmazó összetett műveletek hosszabb ideig nem hajthatóak végre.
• Összetett műveletek felhasználói táblákkal, amelyekben az indexek az optimális végrehajtási tervek biztosítása érdekében tervezhetők, elkerülve a táblavizsgálatokat és a kereséseket az indexvizsgálatok vagy - ideális esetben - az indexkeresések javára.

Az egyidejűség egyéb szempontjai:

• Bizonyos esetekben, amikor az egyedi kulcsokat várhatóan a program beszúrja és frissíti MERGE, megadja az HOLDLOCK egyedi kulcsok megsértésének megelőzését. HOLDLOCK a tranzakcióelkülönítési szint szinonimája SERIALIZABLE , amely nem teszi lehetővé, hogy más egyidejű tranzakciók módosítsák a tranzakció által beolvasott adatokat. SERIALIZABLE a legbiztonságosabb elkülönítési szint, de a legkevésbé egyidejűséget biztosítja más tranzakciókkal, amelyek megőrzik az adattartományokra vonatkozó zárolásokat, hogy megakadályozzák a fantomsorok beszúrását vagy frissítését olvasás közben. További információ: HOLDLOCKTable Hints and SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

JOIN – ajánlott eljárások

Az utasítás teljesítményének javítása és a MERGE megfelelő eredmények elérése érdekében javasoljuk a következő illesztési irányelveket:

• Csak olyan keresési feltételeket adjon meg a ON <merge_search_condition> záradékban, amelyek meghatározzák a forrás- és céltáblákban szereplő adatok egyeztetésének feltételeit. Vagyis csak olyan oszlopokat adjon meg a céltáblából, amelyek a forrástábla megfelelő oszlopaihoz vannak összehasonlítani.
• Ne tartalmazzon más értékekkel, például állandókkal való összehasonlítást.

A forrás- vagy céltáblák sorainak szűréséhez használja az alábbi módszerek egyikét.

• Adja meg a sorszűrés keresési feltételét a megfelelő WHEN záradékban. Például: WHEN NOT MATCHED AND S.EmployeeName LIKE 'S%' THEN INSERT....
• Definiáljon egy nézetet a forráson vagy a célon, amely visszaadja a szűrt sorokat, és hivatkozzon a nézetre forrásként vagy céltáblaként. Ha a nézet a céltáblán van definiálva, az ellene irányuló műveleteknek meg kell felelniük a nézetek frissítésének feltételeinek. Az adatok nézeten keresztüli frissítéséről további információt az Adatok módosítása nézeten keresztül című témakörben talál.
• A záradék használatával kiszűrheti a WITH <common table expression> sorokat a forrás- vagy céltáblákból. Ez a módszer hasonló a záradékban szereplő ON további keresési feltételek megadásához, és helytelen eredményeket eredményezhet. Javasoljuk, hogy a implementálás előtt ne használja ezt a módszert, vagy tesztelje alaposan.

Az utasítás illesztési MERGE művelete ugyanúgy van optimalizálva, mint egy utasítás illesztése SELECT . Vagyis amikor az SQL Server feldolgozza az illesztéseket, a lekérdezésoptimalizáló kiválasztja az illesztés feldolgozásának leghatékonyabb módszerét (több lehetőség közül). Ha a forrás és a cél hasonló méretű, és a korábban ismertetett index-irányelveket alkalmazza a forrás- és céltáblákra, az egyesítési operátor a leghatékonyabb lekérdezési terv. Ennek az az oka, hogy mindkét táblát egyszer ellenőrzi a rendszer, és nincs szükség az adatok rendezésére. Ha a forrás kisebb, mint a céltábla, a beágyazott hurkok operátora előnyösebb.

Egy adott illesztés használatát kényszerítheti az utasítás záradékának OPTION (<query_hint>) megadásávalMERGE. Javasoljuk, hogy ne használja a kivonat illesztése lekérdezési tippként az utasításokhoz MERGE , mert ez az illesztéstípus nem használ indexeket.

A paraméterezés ajánlott eljárásai

Ha egy SELECT, INSERT, , UPDATEvagy DELETE utasítás paraméterek nélkül fut, az SQL Server lekérdezésoptimalizálója dönthet úgy, hogy belsőleg paraméterezi az utasítást. Ez azt jelenti, hogy a lekérdezésben található literális értékek paraméterekkel lesznek helyettesítve. Az utasítás INSERT dbo.MyTable (Col1, Col2) VALUES (1, 10)például belsőleg INSERT dbo.MyTable (Col1, Col2) VALUES (@p1, @p2)is implementálható. Ez az egyszerű paraméterezésnek nevezett folyamat növeli a relációs motor azon képességét, hogy egyezzen az új SQL-utasításokkal a meglévő, korábban lefordított végrehajtási tervekkel. A lekérdezési teljesítmény javulhat, mert a lekérdezés-fordítások és újrafordítások gyakorisága csökken. A lekérdezésoptimalizáló nem alkalmazza az egyszerű paraméterezési folyamatot az utasításokra MERGE . Ezért előfordulhat, MERGE hogy a konstans értékeket tartalmazó utasítások nem hajtanak végre, és nem egyesítenek INSERT, UPDATEvagy DELETE utasításokat, mert minden egyes utasítás végrehajtásakor egy új tervet állít össze a MERGE rendszer.

A lekérdezési teljesítmény javítása érdekében a következő paraméterezési irányelveket javasoljuk:

• A záradékban és az ON <merge_search_condition>WHEN utasítás záradékaiban szereplő összes literális érték paraméterezése MERGE . Az utasítást például belefoglalhatja MERGE egy tárolt eljárásba, amely a literális értékeket a megfelelő bemeneti paraméterekkel helyettesíti.
• Ha nem tudja paraméterezni az utasítást, hozzon létre egy típusterv-útmutatót TEMPLATE , és adja meg a PARAMETERIZATION FORCED lekérdezési tippet a terv útmutatójában. További információ: Lekérdezésparaméterezési viselkedés megadása terv-útmutatók használatával.
• Ha MERGE az utasítások végrehajtása gyakran történik az adatbázisban, fontolja meg az PARAMETERIZATION adatbázis FORCEDbeállítását. A beállításnál körültekintően kell eljárni. A PARAMETERIZATION beállítás egy adatbázisszintű beállítás, amely az adatbázison belüli összes lekérdezés feldolgozását befolyásolja. További információ: Kényszerített paraméterezés.
• Az útmutatók tervezésének újabb és egyszerűbb alternatívaként érdemes lehet hasonló stratégiát használni a Lekérdezéstár tippjeivel. További információ: Query Store-tippek.

TOP záradék – ajánlott eljárások

Az utasításban a MERGETOP záradék a forrástábla és a céltábla összekapcsolása után érintett sorok számát vagy százalékos arányát határozza meg, majd a beszúrási, frissítési vagy törlési műveletre nem jogosult sorok eltávolítása után. A TOP záradék tovább csökkenti az illesztett sorok számát a megadott értékre, a beszúrási, frissítési vagy törlési műveleteket pedig rendezetlen módon alkalmazza a rendszer a többi illesztett sorra. Vagyis nincs olyan sorrend, amelyben a sorok el vannak osztva a WHEN záradékokban meghatározott műveletek között. A beállítás TOP (10) például 10 sort érint; ezek közül 7 frissíthető és 3 beszúrható, illetve 1 törölhető, 5 frissítve és 4 beszúrva stb.

Gyakran használják a TOP záradékot adatmanipulációs nyelv (DML) műveletek végrehajtására egy nagy táblán kötegekben. Ha a TOP záradékot erre a célra használja az MERGE utasításban, fontos tisztában lenni a következő következményekkel.

• Az I/O-teljesítményt befolyásolhatja.

  Az MERGE utasítás a forrás- és a céltáblák teljes táblázatvizsgálatát végzi. A művelet kötegekre való felosztása csökkenti a kötegenként végrehajtott írási műveletek számát; azonban minden köteg teljes táblázatvizsgálatot végez a forrás- és céltáblákon. Az eredményül kapott olvasási tevékenység befolyásolhatja a lekérdezés teljesítményét és a táblákon végzett egyéb egyidejű tevékenységeket.

• Helytelen eredmények fordulhatnak elő.

  Fontos gondoskodni arról, hogy minden egymást követő köteg új sorokat célozhasson meg, vagy nem kívánt viselkedés, például ismétlődő sorok helytelen beszúrása a céltáblába. Ez akkor fordulhat elő, ha a forrástábla olyan sort tartalmaz, amely nem egy cél kötegben volt, hanem a teljes céltáblában volt. A helyes eredmények biztosítása:

  • ON A záradék segítségével meghatározhatja, hogy mely forrássorok érintik a meglévő célsorokat, és melyek valóban újak.
  • A záradék egy WHEN MATCHED további feltételével állapítsa meg, hogy a célsort már frissítette-e egy korábbi köteg.
  • Használjon egy további feltételt a záradékban és WHEN MATCHED a SET logikában annak ellenőrzéséhez, hogy ugyanazt a sort nem lehet kétszer frissíteni.

Mivel a TOP záradék csak a záradékok alkalmazása után lesz alkalmazva, minden végrehajtás vagy beszúr egy valóban nem egyező sort, vagy frissít egy meglévő sort.

Ajánlott tömeges betöltési eljárások

Az MERGE utasítással hatékonyan tölthetők be adatok egy forrásadatfájlból egy céltáblába a OPENROWSET(BULK...) záradék táblaforrásként való megadásával. Ezzel a teljes fájl egyetlen kötegben lesz feldolgozva.

A tömeges egyesítési folyamat teljesítményének javítása érdekében az alábbi irányelveket javasoljuk:

• Hozzon létre egy fürtözött indexet a céltábla illesztőoszlopaiban.

• Tiltsa le a céltábla egyéb nem egyedi, nemclustered indexeit a tömeges betöltés MERGEsorán, majd engedélyezze őket. Ez gyakori és hasznos éjszakai tömeges adatműveletek esetén.

• ORDER A záradékban található UNIQUE tippeket és OPENROWSET(BULK...) tippeket használva adja meg a forrásadatfájl rendezésének módját.

  Alapértelmezés szerint a tömeges művelet feltételezi, hogy az adatfájl rendezetlen. Ezért fontos, hogy a forrásadatok a céltábla fürtözött indexe szerint legyen rendezve, és hogy a ORDER tipp a sorrend jelzésére szolgáljon, hogy a lekérdezésoptimalizáló hatékonyabb lekérdezéstervet hozhasson létre. A tippek futásidőben lesznek érvényesítve; ha az adatfolyam nem felel meg a megadott tippeknek, hibaüzenet jelenik meg.

Ezek az irányelvek biztosítják, hogy az illesztési kulcsok egyediek legyenek, és a forrásfájlban lévő adatok rendezési sorrendje megegyezik a céltáblával. A lekérdezési teljesítmény javul, mert nincs szükség további rendezési műveletekre, és nincs szükség szükségtelen adatmásolatokra.

A MERGE teljesítményének mérése és diagnosztizálása

Az alábbi funkciók segítenek az utasítások teljesítményének mérésében és diagnosztizálásában MERGE .

• A sys.dm_exec_query_optimizer_info dinamikus felügyeleti nézetben az egyesítési stmt számlálóval adja vissza az utasításokhoz tartozó MERGE lekérdezésoptimalizálások számát.
• Használja az attribútumot a merge_action_typesys.dm_exec_plan_attributes dinamikus felügyeleti nézetben az utasítás eredményeként használt trigger-végrehajtási terv típusának MERGE visszaadásához.
• A kiterjesztett események munkamenetével ugyanúgy gyűjthet hibaelhárítási adatokat az MERGE utasításhoz, mint más adatmanipulációs nyelvi (DML-) utasítások esetében. A Bővített események áttekintése további információkért lásd a Rövid útmutatót – Bővített események, és a SSMS XEvent Profilerhasználata című részt.

Examples

A. A MERGE használatával egyetlen utasításban hajthat végre beszúrási és FRISSÍTÉSi műveleteket egy táblán

Gyakori forgatókönyv egy tábla egy vagy több oszlopának frissítése, ha létezik egyező sor. Vagy új sorként is beszúrhatja az adatokat, ha egyező sor nem létezik. Általában mindkét forgatókönyvet úgy hajtja végre, hogy paramétereket ad át egy tárolt eljárásnak, amely tartalmazza a megfelelő UPDATE és INSERT az utasításokat. Az utasítással MERGE mindkét feladatot egyetlen utasításban végezheti el. Az alábbi példa egy AdventureWorks2025 adatbázisban tárolt eljárást mutat, amely mind egy INSERT utasítást, mind egy UPDATE utasítást tartalmaz. Ezt követően a rendszer úgy módosítja az eljárást, hogy egyetlen utasítással MERGE futtassa az egyenértékű műveleteket.

CREATE PROCEDURE dbo.InsertUnitMeasure @UnitMeasureCode NCHAR(3), @Name NVARCHAR(25)
AS
BEGIN
    SET NOCOUNT ON;

    -- Update the row if it exists.
    UPDATE Production.UnitMeasure
    SET Name = @Name
    WHERE UnitMeasureCode = @UnitMeasureCode

    -- Insert the row if the UPDATE statement failed.
    IF (@@ROWCOUNT = 0)
    BEGIN
        INSERT INTO Production.UnitMeasure (
            UnitMeasureCode,
            Name
        )
        VALUES (@UnitMeasureCode, @Name)
    END
END;
GO

-- Test the procedure and return the results.
EXEC InsertUnitMeasure @UnitMeasureCode = 'ABC', @Name = 'Test Value';

SELECT UnitMeasureCode, Name
FROM Production.UnitMeasure
WHERE UnitMeasureCode = 'ABC';
GO

-- Rewrite the procedure to perform the same operations using the
-- MERGE statement.
-- Create a temporary table to hold the updated or inserted values
-- from the OUTPUT clause.
CREATE TABLE #MyTempTable (
    ExistingCode NCHAR(3),
    ExistingName NVARCHAR(50),
    ExistingDate DATETIME,
    ActionTaken NVARCHAR(10),
    NewCode NCHAR(3),
    NewName NVARCHAR(50),
    NewDate DATETIME
);
GO

ALTER PROCEDURE dbo.InsertUnitMeasure @UnitMeasureCode NCHAR(3),
    @Name NVARCHAR(25)
AS
BEGIN
    SET NOCOUNT ON;

    MERGE Production.UnitMeasure AS tgt
    USING (SELECT @UnitMeasureCode, @Name) AS src(UnitMeasureCode, Name)
        ON (tgt.UnitMeasureCode = src.UnitMeasureCode)
    WHEN MATCHED
        THEN
            UPDATE
            SET Name = src.Name
    WHEN NOT MATCHED
        THEN
            INSERT (UnitMeasureCode, Name)
            VALUES (src.UnitMeasureCode, src.Name)
    OUTPUT deleted.*,
        $action,
        inserted.*
    INTO #MyTempTable;
END;
GO

-- Test the procedure and return the results.
EXEC InsertUnitMeasure @UnitMeasureCode = 'ABC', @Name = 'New Test Value';
EXEC InsertUnitMeasure @UnitMeasureCode = 'XYZ', @Name = 'Test Value';
EXEC InsertUnitMeasure @UnitMeasureCode = 'ABC', @Name = 'Another Test Value';

SELECT * FROM #MyTempTable;

-- Cleanup
DELETE FROM Production.UnitMeasure
WHERE UnitMeasureCode IN ('ABC', 'XYZ');

DROP TABLE #MyTempTable;
GO

B. A MERGE használatával egyetlen utasításban hajthat végre FRISSÍTÉSi és TÖRLÉSi műveleteket egy táblán

A következő példa MERGE naponta frissíti a ProductInventory táblázatot az AdventureWorks2025 mintaadatbázisban, a táblázatban feldolgozott SalesOrderDetail megrendelések alapján. A Quantity tábla oszlopa úgy ProductInventory frissül, hogy kivonja az egyes termékekre naponta leadott rendelések számát a SalesOrderDetail táblában. Ha egy termék rendeléseinek száma egy termék készletszintje 0-ra vagy annál kisebbre csökken, a termék sorát a rendszer törli a ProductInventory táblából.

CREATE PROCEDURE Production.usp_UpdateInventory @OrderDate DATETIME
AS
MERGE Production.ProductInventory AS tgt
USING (
    SELECT ProductID,
        SUM(OrderQty)
    FROM Sales.SalesOrderDetail AS sod
    INNER JOIN Sales.SalesOrderHeader AS soh
        ON sod.SalesOrderID = soh.SalesOrderID
            AND soh.OrderDate = @OrderDate
    GROUP BY ProductID
    ) AS src(ProductID, OrderQty)
    ON (tgt.ProductID = src.ProductID)
WHEN MATCHED
    AND tgt.Quantity - src.OrderQty <= 0
    THEN
        DELETE
WHEN MATCHED
    THEN
        UPDATE
        SET tgt.Quantity = tgt.Quantity - src.OrderQty,
            tgt.ModifiedDate = GETDATE()
OUTPUT $action,
    Inserted.ProductID,
    Inserted.Quantity,
    Inserted.ModifiedDate,
    Deleted.ProductID,
    Deleted.Quantity,
    Deleted.ModifiedDate;
GO

EXECUTE Production.usp_UpdateInventory '20030501';

C. A MERGE használatával update és INSERT műveleteket hajthat végre egy céltáblán egy származtatott forrástáblával

A következő példa MERGE az AdventureWorks2025 adatbázisban a SalesReason táblázat módosítására szolgál sorok frissítésével vagy hozzáadásával.

Ha a forrástábla értéke NewName megegyezik a Name céltábla oszlopában lévő értékkel, azSalesReasonReasonType oszlop frissül a céltáblában. Ha az érték NewName nem egyezik, a forrássor be lesz szúrva a céltáblába. A forrástábla egy származtatott tábla, amely a Transact-SQL tábla értékkonstruktorával több sort ad meg a forrástábla számára. A táblaérték-konstruktor származtatott táblában való használatáról további információt a Table Value Constructor (Transact-SQL) című témakörben talál.

A OUTPUT záradék hasznos lehet az utasítások eredményének MERGE lekérdezéséhez. További információ: OUTPUT záradék (Transact-SQL). A példa azt is bemutatja, hogyan tárolhatja a OUTPUT záradék eredményeit egy táblaváltozóban. Ezután összegzi az MERGE utasítás eredményeit egy egyszerű kiválasztási művelet futtatásával, amely visszaadja a beszúrt és frissített sorok számát.

-- Create a temporary table variable to hold the output actions.
DECLARE @SummaryOfChanges TABLE (Change VARCHAR(20));

MERGE INTO Sales.SalesReason AS tgt
USING (
    VALUES ('Recommendation', 'Other'),
        ('Review', 'Marketing'),
        ('Internet', 'Promotion')
    ) AS src(NewName, NewReasonType)
    ON tgt.Name = src.NewName
WHEN MATCHED
    THEN
        UPDATE
        SET ReasonType = src.NewReasonType
WHEN NOT MATCHED BY TARGET
    THEN
        INSERT (Name, ReasonType)
        VALUES (NewName, NewReasonType)
OUTPUT $action
INTO @SummaryOfChanges;

-- Query the results of the table variable.
SELECT Change,
    COUNT(*) AS CountPerChange
FROM @SummaryOfChanges
GROUP BY Change;

D. A MERGE utasítás eredményeinek beszúrása egy másik táblába

Az alábbi példa egy OUTPUT utasítás záradékából MERGE visszaadott adatokat rögzíti, és beszúrja az adatokat egy másik táblába. Az MERGE állítás frissíti a Quantity táblázat oszlopát ProductInventory az AdventureWorks2025 adatbázisban, a táblázatban feldolgozott SalesOrderDetail rendelések alapján. A példa rögzíti a frissített sorokat, és beszúrja őket egy másik táblába, amely a készletváltozások nyomon követésére szolgál.

CREATE TABLE Production.UpdatedInventory (
    ProductID INT NOT NULL,
    LocationID INT,
    NewQty INT,
    PreviousQty INT,
    CONSTRAINT PK_Inventory PRIMARY KEY CLUSTERED (
        ProductID,
        LocationID
        )
    );
GO

INSERT INTO Production.UpdatedInventory
SELECT ProductID, LocationID, NewQty, PreviousQty
FROM (
    MERGE Production.ProductInventory AS pi
    USING (
        SELECT ProductID, SUM(OrderQty)
        FROM Sales.SalesOrderDetail AS sod
        INNER JOIN Sales.SalesOrderHeader AS soh
            ON sod.SalesOrderID = soh.SalesOrderID
                AND soh.OrderDate BETWEEN '20030701'
                    AND '20030731'
        GROUP BY ProductID
        ) AS src(ProductID, OrderQty)
        ON pi.ProductID = src.ProductID
    WHEN MATCHED
        AND pi.Quantity - src.OrderQty >= 0
        THEN
            UPDATE SET pi.Quantity = pi.Quantity - src.OrderQty
    WHEN MATCHED
        AND pi.Quantity - src.OrderQty <= 0
        THEN
            DELETE
    OUTPUT $action,
        Inserted.ProductID,
        Inserted.LocationID,
        Inserted.Quantity AS NewQty,
        Deleted.Quantity AS PreviousQty
    ) AS Changes(Action, ProductID, LocationID, NewQty, PreviousQty)
WHERE Action = 'UPDATE';
GO

E. A MERGE használatával szúrhat be vagy frissíthet egy gráfadatbázis cél éltábláján

Ebben a példában csomóponttáblákat Person és City egy éltáblát livesInhoz létre. Az élen lévő MERGElivesIn utasítást használva szúrjon be egy új sort, ha az él még nem létezik az és PersonCity a között. Ha az él már létezik, akkor csak frissítse a StreetAddress attribútumot a livesIn peremhálózaton.

-- CREATE node and edge tables
CREATE TABLE Person
(
    ID INTEGER PRIMARY KEY,
    PersonName VARCHAR(100)
)
AS NODE
GO

CREATE TABLE City
(
    ID INTEGER PRIMARY KEY,
    CityName VARCHAR(100),
    StateName VARCHAR(100)
)
AS NODE
GO

CREATE TABLE livesIn
(
    StreetAddress VARCHAR(100)
)
AS EDGE
GO

-- INSERT some test data into node and edge tables
INSERT INTO Person VALUES (1, 'Ron'), (2, 'David'), (3, 'Nancy')
GO

INSERT INTO City VALUES (1, 'Redmond', 'Washington'), (2, 'Seattle', 'Washington')
GO

INSERT livesIn SELECT P.$node_id, C.$node_id, c
FROM Person P, City C, (values (1,1, '123 Avenue'), (2,2,'Main Street')) v(a,b,c)
WHERE P.id = a AND C.id = b
GO

-- Use MERGE to update/insert edge data
CREATE OR ALTER PROCEDURE mergeEdge
    @PersonId integer,
    @CityId integer,
    @StreetAddress varchar(100)
AS
BEGIN
    MERGE livesIn
        USING ((SELECT @PersonId, @CityId, @StreetAddress) AS T (PersonId, CityId, StreetAddress)
                JOIN Person ON T.PersonId = Person.ID
                JOIN City ON T.CityId = City.ID)
        ON MATCH (Person-(livesIn)->City)
    WHEN MATCHED THEN
        UPDATE SET StreetAddress = @StreetAddress
    WHEN NOT MATCHED THEN
        INSERT ($from_id, $to_id, StreetAddress)
        VALUES (Person.$node_id, City.$node_id, @StreetAddress) ;
END
GO

-- Following will insert a new edge in the livesIn edge table
EXEC mergeEdge 3, 2, '4444th Avenue'
GO

-- Following will update the StreetAddress on the edge that connects Ron to Redmond
EXEC mergeEdge 1, 1, '321 Avenue'
GO

-- Verify that all the address were added/updated correctly
SELECT PersonName, CityName, StreetAddress
FROM Person , City , livesIn
WHERE MATCH(Person-(livesIn)->city)
GO

Kapcsolódó tartalom

• VÁLASZT (Transact-SQL)
• BEHELYEZKEDÉS (Transact-SQL)
• FRISSÍTÉS (Transact-SQL)
• TÖRLÉS (Transact-SQL)
• OUTPUT záradék (Transact-SQL)
• MERGE az Integrációs Szolgáltatások Csomagokban
• FROM klauszula és JOIN, APPLY, PIVOT (Transact-SQL)
• Táblaérték-konstruktor (Transact-SQL)