MERGE (Transact-SQL)

Van toepassing op:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsSQL-database in Microsoft Fabric Preview

Met de instructie MERGE worden invoeg-, bijwerk- of verwijderbewerkingen uitgevoerd op een doeltabel uit de resultaten van een join met een brontabel. Synchroniseer bijvoorbeeld twee tabellen door rijen in één tabel in te voegen, bij te werken of te verwijderen op basis van verschillen in de andere tabel.

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);

Transact-SQL syntaxis-conventies

Syntax

[ 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>

[ WITH <common_table_expression> [,...n] ]
MERGE
    [ INTO ] <target_table> [ [ 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 ]
    [ OPTION ( <query_hint> [ ,...n ] ) ]
;  -- The semi-colon is required, or the query will return a syntax error.

<target_table> ::=
{
    [ database_name . schema_name . | schema_name . ]
  target_table
}

<merge_search_condition> ::=
    <search_condition>

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

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

<clause_search_condition> ::=
    <search_condition>

Arguments

MET <common_table_expression>

Hiermee geeft u de tijdelijke benoemde resultatenset of weergave, ook wel algemene tabelexpressie genoemd, die is gedefinieerd binnen het bereik van de MERGE-instructie. De resultatenset is afgeleid van een eenvoudige query en wordt verwezen door de MERGE-instructie. Zie WITH common_table_expression (Transact-SQL)voor meer informatie.

database_name

De naam van de database waarin target_table zich bevindt.

schema_name

De naam van het schema waartoe target_table behoort.

target_table

De tabel of weergave waaruit de gegevensrijen <table_source> afkomstig zijn, worden vergeleken op <clause_search_condition>basis van . target_table is het doel van alle invoeg-, update- of verwijderbewerkingen die zijn opgegeven door de WHEN-componenten van de MERGE-instructie.

Als target_table een weergave is, moeten alle acties hierop voldoen aan de voorwaarden voor het bijwerken van weergaven. Zie Gegevens wijzigen via een weergave-voor meer informatie.

target_table kan geen externe tabel zijn. target_table geen regels kunnen hebben gedefinieerd. target_table kan geen tabel zijn die is geoptimaliseerd voor geheugen.

Hints kunnen worden opgegeven als een <merge_hint>.

[ AS ] table_alias

Een alternatieve naam om te verwijzen naar een tabel voor de target_table.

TABLE_SOURCE <gebruiken>

Hiermee geeft u de gegevensbron op die overeenkomt met de gegevensrijen in target_table op <merge_search_condition>basis van . Het resultaat van deze overeenkomst bepaalt de acties die moeten worden uitgevoerd door de WHEN-componenten van de MERGE-instructie. <table_source> kan een externe tabel of een afgeleide tabel zijn die toegang heeft tot externe tabellen.

[ AS ] table_alias

Een alternatieve naam om te verwijzen naar een tabel voor de table_source.

Zie FROM (Transact-SQL)voor meer informatie over de syntaxis en argumenten van deze component.

OP <merge_search_condition>

Hiermee geeft u de voorwaarden op waarop <table_source> joins met target_table om te bepalen waar ze overeenkomen.

WANNEER DEZE OVEREENKOMEN, <merge_matched>

Hiermee geeft u op dat alle rijen van *target_table, die overeenkomen met de rijen die door <table_source> ON <merge_search_condition>worden geretourneerd, en voldoen aan een aanvullende zoekvoorwaarde, worden bijgewerkt of verwijderd volgens de <merge_matched> component.

De MERGE-instructie kan maximaal twee WHEN MATCHED-componenten bevatten. Als er twee componenten zijn opgegeven, moet de eerste component vergezeld gaan van een AND-component <search_condition> . Voor een bepaalde rij wordt de tweede WHEN MATCHED-component alleen toegepast als de eerste niet is. Als er twee WHEN MATCHED-componenten zijn, moet er een UPDATE-actie worden opgegeven en moet er een DELETE-actie worden opgegeven. Wanneer UPDATE is opgegeven in de <merge_matched> component en meer dan één rij overeenkomt met een rij in target_table op <merge_search_condition>basis van<table_source>, retourneert SQL Server een fout. De MERGE-instructie kan niet meer dan één keer dezelfde rij bijwerken of dezelfde rij bijwerken en verwijderen.

WANNEER DEZE NIET OVEREENKOMEN [ PER DOEL ] MERGE_NOT_MATCHED <>

Hiermee geeft u op dat een rij wordt ingevoegd in target_table voor elke rij die wordt geretourneerd door <table_source> ON <merge_search_condition> die niet overeenkomt met een rij in target_table, maar voldoet aan een extra zoekvoorwaarde, indien aanwezig. De in te voegen waarden worden opgegeven door de <merge_not_matched> component. De MERGE-instructie kan slechts één component HEBBEN WANNEER NIET OVEREENKOMEND [ BY TARGET ] component.

WANNEER DEZE NIET OVEREENKOMEN MET DE BRON, <MERGE_MATCHED>

Hiermee geeft u op dat alle rijen van *target_table, die niet overeenkomen met de rijen die worden geretourneerd door <table_source> ON <merge_search_condition>, en die voldoen aan een aanvullende zoekvoorwaarde, worden bijgewerkt of verwijderd volgens de <merge_matched> component.

De MERGE-instructie kan maximaal twee ALS NIET OVEREENKOMEN MET SOURCE-componenten zijn. Als er twee componenten zijn opgegeven, moet de eerste component vergezeld gaan van een AND-component <clause_search_condition> . Voor een bepaalde rij wordt de tweede WHEN NOT MATCHED BY SOURCE-component alleen toegepast als de eerste niet is. Als er twee NIET OVEREENKOMEN MET BRONcomponenten zijn, moet u een UPDATE-actie opgeven en moet u een DELETE-actie opgeven. Alleen kolommen uit de doeltabel kunnen worden verwezen in <clause_search_condition>.

Wanneer er geen rijen worden geretourneerd door <table_source>, kunnen kolommen in de brontabel niet worden geopend. Als de update- of verwijderactie die is opgegeven in de <merge_matched> component verwijst naar kolommen in de brontabel, wordt fout 207 (ongeldige kolomnaam) geretourneerd. De component WHEN NOT MATCHED BY SOURCE THEN UPDATE SET TargetTable.Col1 = SourceTable.Col1 kan bijvoorbeeld ertoe leiden dat de instructie mislukt omdat Col1 in de brontabel niet toegankelijk is.

AND <clause_search_condition>

Hiermee geeft u een geldige zoekvoorwaarde op. Zie zoekvoorwaarde (Transact-SQL)voor meer informatie.

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

Hiermee geeft u op dat optimizer-hints worden gebruikt om de manier aan te passen waarop de database-engine de instructie verwerkt. Zie Hints (Transact-SQL) - Query voor meer informatie.

<merge_matched>

Hiermee geeft u de update- of verwijderactie op die wordt toegepast op alle rijen van target_table die niet overeenkomen met de rijen die door <table_source> ON <merge_search_condition>worden geretourneerd en die voldoen aan een aanvullende zoekvoorwaarde.

UPDATE SET <set_clause>

Hiermee geeft u de lijst met kolom- of variabelenamen die moeten worden bijgewerkt in de doeltabel en de waarden waarmee ze moeten worden bijgewerkt.

Zie UPDATE (Transact-SQL) voor meer informatie over de argumenten van deze component. Het instellen van een variabele op dezelfde waarde als een kolom wordt niet ondersteund.

DELETE

Hiermee geeft u op dat de rijen die overeenkomen met rijen in target_table worden verwijderd.

<merge_not_matched>

Hiermee geeft u de waarden op die moeten worden ingevoegd in de doeltabel.

( column_list )

Een lijst met een of meer kolommen van de doeltabel waarin gegevens moeten worden ingevoegd. Kolommen moeten worden opgegeven als een naam van één onderdeel, anders mislukt de MERGE-instructie. column_list moet tussen haakjes staan en gescheiden door komma's.

VALUES ( values_list )

Een door komma's gescheiden lijst met constanten, variabelen of expressies die waarden retourneren die moeten worden ingevoegd in de doeltabel. Expressies kunnen geen EXECUTE-instructie bevatten.

<search_condition>

Hiermee geeft u de zoekvoorwaarden op die moeten worden opgegeven <merge_search_condition> of <clause_search_condition>. Zie Zoekvoorwaarde (Transact-SQL)voor meer informatie over de argumenten voor deze component.

Remarks

Ten minste één van de drie MATCHED-componenten moet worden opgegeven, maar ze kunnen in elke volgorde worden opgegeven. Een variabele kan niet meer dan één keer worden bijgewerkt in dezelfde MATCHED-component.

Elke invoeg-, update- of verwijderactie die is opgegeven in de doeltabel door de MERGE-instructie, wordt beperkt door beperkingen die erop zijn gedefinieerd, inclusief trapsgewijze beperkingen voor referentiële integriteit. Als IGNORE_DUP_KEY is ingeschakeld voor unieke indexen in de doeltabel, negeert MERGE deze instelling.

Voor de MERGE-instructie is een puntkomma (;) als eindinstructie vereist. Fout 10713 wordt gegenereerd wanneer een MERGE-instructie wordt uitgevoerd zonder de afsluiter.

Wanneer deze wordt gebruikt na SAMENVOEGEN, retourneert @@ROWCOUNT (Transact-SQL) het totale aantal rijen dat is ingevoegd, bijgewerkt en verwijderd naar de client.

MERGE is een volledig gereserveerd trefwoord wanneer het compatibiliteitsniveau van de database is ingesteld op 100 of hoger. De MERGE-instructie is beschikbaar onder zowel 90100 databasecompatibiliteitsniveaus als databasecompatibiliteitsniveau. Het trefwoord is echter niet volledig gereserveerd wanneer het compatibiliteitsniveau van de database is ingesteld op 90.

-- Case 1
SELECT a.name,
    c.distribution_policy_desc,
    b.type
FROM sys.tables a
INNER JOIN sys.indexes b
    ON a.object_id = b.object_id
INNER JOIN sys.pdw_table_distribution_properties c
    ON a.object_id = c.object_id
WHERE b.type = 2
    AND c.distribution_policy_desc = 'HASH';

-- Subject to Case 2, if distribution key value is updated in MERGE statement
SELECT a.name,
    c.distribution_policy_desc
FROM sys.tables a
INNER JOIN sys.pdw_table_distribution_properties c
    ON a.object_id = c.object_id
WHERE c.distribution_policy_desc = 'HASH';

IF object_id('[check_table_1]', 'U') IS NOT NULL
    DROP TABLE [check_table_1]
GO

IF object_id('[check_table_2]', 'U') IS NOT NULL
    DROP TABLE [check_table_2]
GO

CREATE TABLE [check_table_1]
    WITH (DISTRIBUTION = ROUND_ROBIN) AS

SELECT <DISTRIBUTION_COLUMN> AS x
FROM <MERGE_TABLE>
GROUP BY <DISTRIBUTION_COLUMN>;
GO

CREATE TABLE [check_table_2]
    WITH (DISTRIBUTION = HASH (x)) AS

SELECT x
FROM [check_table_1];
GO

IF NOT EXISTS (
        SELECT TOP 1 *
        FROM (
            SELECT <DISTRIBUTION_COLUMN> AS x
            FROM <MERGE_TABLE>

            EXCEPT

            SELECT x
            FROM [check_table_2]
            ) AS tmp
        )
    SELECT 'no need for repair' AS result
ELSE
    SELECT 'needs repair' AS result
GO

IF object_id('[check_table_1]', 'U') IS NOT NULL
    DROP TABLE [check_table_1]
GO

IF object_id('[check_table_2]', 'U') IS NOT NULL
    DROP TABLE [check_table_2]
GO

IF object_id('[repair_table_temp]', 'U') IS NOT NULL
    DROP TABLE [repair_table_temp];
GO

IF object_id('[repair_table]', 'U') IS NOT NULL
    DROP TABLE [repair_table];
GO

CREATE TABLE [repair_table_temp]
    WITH (DISTRIBUTION = ROUND_ROBIN) AS

SELECT *
FROM <MERGE_TABLE>;
GO

-- [repair_table] will hold the repaired table generated from <MERGE_TABLE>
CREATE TABLE [repair_table]
    WITH (DISTRIBUTION = HASH (<DISTRIBUTION_COLUMN>)) AS

SELECT *
FROM [repair_table_temp];
GO

IF object_id('[repair_table_temp]', 'U') IS NOT NULL
    DROP TABLE [repair_table_temp];
GO

Trigger implementation

Voor elke invoeg-, bijwerk- of verwijderactie die is opgegeven in de instructie MERGE, activeert SQL Server alle bijbehorende AFTER-triggers die zijn gedefinieerd in de doeltabel, maar garandeert niet welke actie wordt geactiveerd als eerste of laatste. Triggers die zijn gedefinieerd voor dezelfde actie, respecteren de volgorde die u opgeeft. Zie Eerste en Laatste triggers opgeven voor meer informatie over het instellen van een triggervolgorde.

Als voor de doeltabel een in plaats van een trigger is gedefinieerd voor een invoeg-, bijwerk- of verwijderactie die wordt uitgevoerd door een MERGE-instructie, moet deze een ingeschakelde IN PLAATS VAN trigger hebben voor alle acties die zijn opgegeven in de MERGE-instructie.

Als er IN PLAATS VAN UPDATE- of IN PLAATS VAN DELETE-triggers zijn gedefinieerd op target_table, worden de update- of verwijderbewerkingen niet uitgevoerd. In plaats daarvan worden de triggers geactiveerd en worden de ingevoegde en verwijderde tabellen vervolgens dienovereenkomstig ingevuld.

Als er IN PLAATS VAN INSERT-triggers zijn gedefinieerd op target_table, wordt de invoegbewerking niet uitgevoerd. In plaats daarvan wordt de tabel dienovereenkomstig ingevuld.

Permissions

Vereist select-machtigingen voor de brontabel en de machtigingen INSERT, UPDATE of DELETE voor de doeltabel. Zie de sectie Machtigingen in de artikelen SELECT, INSERT, UPDATE en DELETE voor meer informatie.

Best practices voor indexen

Met behulp van de MERGE-instructie kunt u de afzonderlijke DML-instructies vervangen door één instructie. Dit kan de queryprestaties verbeteren omdat de bewerkingen binnen één instructie worden uitgevoerd, waardoor het aantal keren dat de gegevens in de bron- en doeltabellen worden verwerkt, wordt geminimaliseerd. Prestatieverbeteringen zijn echter afhankelijk van de juiste indexen, joins en andere overwegingen.

Om de prestaties van de MERGE-instructie te verbeteren, raden we de volgende indexrichtlijnen aan:

• Maak indexen om de join tussen de bron en het doel van de MERGE te vergemakkelijken:
  • Maak een index voor de joinkolommen in de brontabel met sleutels voor de joinlogica voor de doeltabel. Indien mogelijk moet deze uniek zijn.
  • Maak ook een index voor de joinkolommen in de doeltabel. Indien mogelijk moet dit een unieke geclusterde index zijn.
  • Deze twee indexen zorgen ervoor dat de gegevens in de tabellen worden gesorteerd, en uniekheid helpt de prestaties van de vergelijking. De prestaties van query's worden verbeterd omdat de optimalisatiefunctie voor query's geen extra validatieverwerking hoeft uit te voeren om dubbele rijen te zoeken en bij te werken en extra sorteerbewerkingen niet nodig zijn.
• Vermijd tabellen met een willekeurige vorm van columnstore-index als doel van MERGE-instructies. Net als bij updates kunt u de prestaties verbeteren met columnstore-indexen door een gefaseerde rowstore-tabel bij te werken en vervolgens een batch DELETE en INSERT uit te voeren in plaats van een UPDATE of MERGE.

Gelijktijdigheidsoverwegingen voor SAMENVOEGEN

In termen van vergrendeling verschilt MERGE van discrete, opeenvolgende INSERT-, UPDATE- en DELETE-instructies. MERGE voert nog steeds INSERT-, UPDATE- en DELETE-bewerkingen uit, maar maakt gebruik van verschillende vergrendelingsmechanismen. Het kan efficiënter zijn om discrete INSERT-, UPDATE- en DELETE-instructies te schrijven voor bepaalde toepassingsbehoeften. Op schaal kan MERGE ingewikkelde gelijktijdigheidsproblemen veroorzaken of geavanceerde probleemoplossing vereisen. Als zodanig moet u een MERGE-instructie grondig testen voordat u implementeert in productie.

MERGE-instructies zijn een geschikte vervanging voor discrete INSERT-, UPDATE- en DELETE-bewerkingen in (maar niet beperkt tot) de volgende scenario's:

• ETL-bewerkingen met betrekking tot grote rijen worden uitgevoerd tijdens een tijd waarin andere gelijktijdige bewerkingen niet* worden verwacht. Wanneer een zware gelijktijdigheid wordt verwacht, kan de afzonderlijke logica INSERT, UPDATE en DELETE beter presteren, met minder blokkeringen dan een MERGE-instructie.
• Complexe bewerkingen waarbij kleine rijen worden geteld en transacties worden waarschijnlijk niet uitgevoerd voor een langere duur.
• Complexe bewerkingen met behulp van gebruikerstabellen waarbij indexen kunnen worden ontworpen om optimale uitvoeringsplannen te garanderen, tabelscans en zoekacties te vermijden ten gunste van indexscans of - idealiter - indexzoekingen.

Andere overwegingen voor gelijktijdigheid:

• In sommige scenario's waarin wordt verwacht dat unieke sleutels zowel worden ingevoegd als bijgewerkt door samenvoegen, zal het opgeven van de HOLDLOCK voorkomen dat unieke sleutelschendingen worden geschonden. HOLDLOCK is een synoniem voor het SERIALIZABLE transactieisolatieniveau, waardoor andere gelijktijdige transacties geen gegevens kunnen wijzigen die door deze transactie zijn gelezen. SERIALIZABLE is het veiligste isolatieniveau, maar biedt de minste gelijktijdigheid met andere transacties die vergrendelingen van gegevensbereiken behouden om te voorkomen dat fantoomrijen worden ingevoegd of bijgewerkt terwijl leesbewerkingen worden uitgevoerd. Zie Hints en SET TRANSACTION ISOLATION LEVEL (Transact-SQL) voor meer informatie over HOLDLOCK.

Best practices voor JOIN

Om de prestaties van de MERGE-instructie te verbeteren en ervoor te zorgen dat de juiste resultaten worden verkregen, raden we de volgende joinrichtlijnen aan:

• Geef alleen zoekvoorwaarden op in de component ON <merge_search_condition> die de criteria bepalen voor overeenkomende gegevens in de bron- en doeltabellen. Dat wil gezegd, geef alleen kolommen op uit de doeltabel die worden vergeleken met de bijbehorende kolommen van de brontabel.
• Neem geen vergelijkingen op met andere waarden, zoals een constante.

Als u rijen uit de bron- of doeltabellen wilt filteren, gebruikt u een van de volgende methoden.

• Geef de zoekvoorwaarde op voor het filteren van rijen in de juiste WHEN-component. Bijvoorbeeld WHEN NOT MATCHED AND S.EmployeeName LIKE 'S%' THEN INSERT....
• Definieer een weergave op de bron of het doel die de gefilterde rijen retourneert en verwijst naar de weergave als de bron- of doeltabel. Als de weergave is gedefinieerd in de doeltabel, moeten alle acties hierop voldoen aan de voorwaarden voor het bijwerken van weergaven. Zie Gegevens wijzigen via een weergave voor meer informatie over het bijwerken van gegevens met behulp van een weergave.
• Gebruik de WITH <common table expression> component om rijen uit de bron- of doeltabellen te filteren. Deze methode is vergelijkbaar met het opgeven van aanvullende zoekcriteria in de ON-component en kan onjuiste resultaten opleveren. U wordt aangeraden deze methode niet te gebruiken of grondig te testen voordat u deze implementeert.

De join-bewerking in de MERGE-instructie is op dezelfde manier geoptimaliseerd als een join in een SELECT-instructie. Wanneer SQL Server-processen worden samengevoegd, kiest de query optimizer de meest efficiënte methode (uit verschillende mogelijkheden) voor het verwerken van de join. Wanneer de bron en het doel een vergelijkbare grootte hebben en de eerder beschreven indexrichtlijnen worden toegepast op de bron- en doeltabellen, is een samenvoegingsoperator het meest efficiënte queryplan. Dit komt doordat beide tabellen eenmaal worden gescand en de gegevens niet hoeven te worden gesorteerd. Wanneer de bron kleiner is dan de doeltabel, heeft een operator voor geneste lussen de voorkeur.

U kunt het gebruik van een specifieke join afdwingen door de OPTION (<query_hint>) component in de MERGE-instructie op te geven. U wordt aangeraden de hash-join niet te gebruiken als een queryhint voor MERGE-instructies, omdat dit jointype geen indexen gebruikt.

Aanbevolen procedures voor parameterisatie

Als een SELECT-, INSERT-, UPDATE- of DELETE-instructie zonder parameters wordt uitgevoerd, kan de SQL Server-queryoptimalisatie ervoor kiezen om de instructie intern te parameteriseren. Dit betekent dat letterlijke waarden die zijn opgenomen in de query, worden vervangen door parameters. De instructie INSERT dbo.MyTable (Col1, Col2) VALUES (1, 10)kan bijvoorbeeld intern worden geïmplementeerd als INSERT dbo.MyTable (Col1, Col2) VALUES (@p1, @p2). Dit proces, dat eenvoudige parameterisatie wordt genoemd, verhoogt de mogelijkheid van de relationele engine om nieuwe SQL-instructies te koppelen aan bestaande, eerder gecompileerde uitvoeringsplannen. De queryprestaties kunnen worden verbeterd omdat de frequentie van querycompilaties en hercompilaties wordt verminderd. De queryoptimalisatie past het eenvoudige parameterisatieproces niet toe op MERGE-instructies. Daarom kunnen MERGE-instructies met letterlijke waarden mogelijk geen afzonderlijke INSERT-, UPDATE- of DELETE-instructies uitvoeren omdat een nieuw plan wordt gecompileerd telkens wanneer de MERGE-instructie wordt uitgevoerd.

Om de prestaties van query's te verbeteren, raden we de volgende richtlijnen voor parameterisatie aan:

• Parameteriseer alle letterlijke waarden in de ON <merge_search_condition> component en in de WHEN componenten van de MERGE-instructie. U kunt de INSTRUCTIE MERGE bijvoorbeeld opnemen in een opgeslagen procedure, waarbij de letterlijke waarden worden vervangen door de juiste invoerparameters.
• Als u de instructie niet kunt parameteriseren, maakt u een planhandleiding van het type TEMPLATE en geeft u de PARAMETERIZATION FORCED queryhint op in de planhandleiding. Zie Queryparameterisatiegedrag opgeven met behulp van planhandleidingenvoor meer informatie.
• Als MERGE-instructies regelmatig worden uitgevoerd in de database, kunt u overwegen om de optie PARAMETERISATIE in de database in te stellen op GEFORCEERD. Wees voorzichtig bij het instellen van deze optie. De PARAMETERIZATION optie is een instelling op databaseniveau en is van invloed op de wijze waarop alle query's voor de database worden verwerkt. Zie Geforceerde parameterisatie voor meer informatie.
• Als nieuwer en eenvoudiger alternatief voor het plannen van handleidingen kunt u een vergelijkbare strategie overwegen met Query Store-hints. Zie Query Store-hints voor meer informatie.

Best practices voor TOP-componenten

In de instructie MERGE geeft de COMPONENT TOP het aantal of het percentage rijen op dat wordt beïnvloed na de brontabel en de doeltabel, en na rijen die niet in aanmerking komen voor een actie invoegen, bijwerken of verwijderen, worden verwijderd. De TOP-component vermindert het aantal gekoppelde rijen verder tot de opgegeven waarde en de invoeg-, update- of verwijderacties worden op niet-geordende wijze toegepast op de resterende gekoppelde rijen. Dat wil gezegd, er is geen volgorde waarin de rijen worden verdeeld over de acties die zijn gedefinieerd in de WHEN-componenten. Als u bijvoorbeeld TOP (10) opgeeft, is dit van invloed op 10 rijen; van deze rijen kunnen 7 worden bijgewerkt en 3 ingevoegd, of 1 kan worden verwijderd, 5 bijgewerkt en 4 ingevoegd, enzovoort.

Het is gebruikelijk om de TOP-component te gebruiken om DML-bewerkingen (Data Manipulat Language) uit te voeren op een grote tabel in batches. Wanneer u voor dit doel de TOP-component in de MERGE-instructie gebruikt, is het belangrijk om de volgende implicaties te begrijpen.

• I/O-prestaties kunnen worden beïnvloed.

  De MERGE-instructie voert een volledige tabelscan uit van zowel de bron- als doeltabellen. Het delen van de bewerking in batches vermindert het aantal schrijfbewerkingen dat per batch wordt uitgevoerd; Elke batch voert echter een volledige tabelscan uit van de bron- en doeltabellen. De resulterende leesactiviteit kan van invloed zijn op de prestaties van de query en andere gelijktijdige activiteit in de tabellen.

• Onjuiste resultaten kunnen optreden.

  Het is belangrijk om ervoor te zorgen dat alle opeenvolgende batches nieuwe rijen of ongewenst gedrag toepassen, zoals het onjuist invoegen van dubbele rijen in de doeltabel. Dit kan gebeuren wanneer de brontabel een rij bevat die zich niet in een doelbatch bevindt, maar zich in de algemene doeltabel bevindt. Ga als volgt te werk om de juiste resultaten te garanderen:

  • Gebruik de ON-component om te bepalen welke bronrijen van invloed zijn op bestaande doelrijen en welke echt nieuw zijn.
  • Gebruik een extra voorwaarde in de component WHEN MATCHED om te bepalen of de doelrij al is bijgewerkt door een vorige batch.
  • Gebruik een extra voorwaarde in de component WHEN MATCHED en SET-logica om te controleren of dezelfde rij niet tweemaal kan worden bijgewerkt.

Omdat de TOP-component alleen wordt toegepast nadat deze componenten zijn toegepast, voegt elke uitvoering een echt niet-overeenkomende rij in of werkt één bestaande rij bij.

Best practices voor bulksgewijs laden

De MERGE-instructie kan worden gebruikt om efficiënt gegevens uit een brongegevensbestand bulksgewijs te laden in een doeltabel door de OPENROWSET(BULK...) component op te geven als de tabelbron. Hierdoor wordt het hele bestand in één batch verwerkt.

Om de prestaties van het bulksgewijs samenvoegen te verbeteren, raden we de volgende richtlijnen aan:

• Maak een geclusterde index voor de joinkolommen in de doeltabel.

• Schakel andere niet-unieke, niet-geclusterde indexen in de doeltabel uit tijdens bulksgewijs samenvoegen en schakel ze daarna in. Dit is gebruikelijk en nuttig voor nachtelijke bulkgegevensbewerkingen.

• Gebruik de hints ORDER en UNIQUE in de OPENROWSET(BULK...) component om op te geven hoe het brongegevensbestand wordt gesorteerd.

  Standaard wordt bij de bulkbewerking ervan uitgegaan dat het gegevensbestand niet is gerangschikt. Daarom is het belangrijk dat de brongegevens worden gesorteerd op basis van de geclusterde index in de doeltabel en dat de HINT ORDER wordt gebruikt om de volgorde aan te geven, zodat de queryoptimalisatie een efficiënter queryplan kan genereren. Hints worden tijdens runtime gevalideerd; Als de gegevensstroom niet voldoet aan de opgegeven hints, wordt er een fout gegenereerd.

Deze richtlijnen zorgen ervoor dat de joinsleutels uniek zijn en de sorteervolgorde van de gegevens in het bronbestand overeenkomt met de doeltabel. Queryprestaties worden verbeterd omdat er geen extra sorteerbewerkingen nodig zijn en onnodige gegevenskopieën niet nodig zijn.

Samenvoegprestaties meten en diagnosticeren

De volgende functies zijn beschikbaar om u te helpen bij het meten en diagnosticeren van de prestaties van MERGE-instructies.

• Gebruik de samenvoegteller stmt in de sys.dm_exec_query_optimizer_info dynamische beheerweergave om het aantal queryoptimalisaties te retourneren dat voor MERGE-instructies is.
• Gebruik het merge_action_type kenmerk in de weergave sys.dm_exec_plan_attributes dynamisch beheer om het type triggeruitvoeringsplan te retourneren dat wordt gebruikt als resultaat van een MERGE-instructie.
• Gebruik een uitgebreide gebeurtenissessie om probleemoplossingsgegevens voor de MERGE-instructie op dezelfde manier te verzamelen als voor andere DML-instructies (Data Manipulat Language). Zie Quickstart: Uitgebreide gebeurtenissen en De SSMS XEvent Profiler-gebruiken voor meer informatie over overzicht van uitgebreidegebeurtenissen.

Examples

A. Merge gebruiken om INSERT- en UPDATE-bewerkingen in een tabel in één instructie uit te voeren

Een veelvoorkomend scenario is het bijwerken van een of meer kolommen in een tabel als er een overeenkomende rij bestaat. U kunt de gegevens ook invoegen als een nieuwe rij als er geen overeenkomende rij bestaat. Meestal voert u een van beide scenario's uit door parameters door te geven aan een opgeslagen procedure die de juiste UPDATE- en INSERT-instructies bevat. Met de instructie MERGE kunt u beide taken in één instructie uitvoeren. In het volgende voorbeeld ziet u een opgeslagen procedure in de Database AdventureWorks2022 die zowel een INSERT-instructie als een UPDATE-instructie bevat. De procedure wordt vervolgens aangepast om de equivalente bewerkingen uit te voeren met behulp van één MERGE-instructie.

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

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.
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);
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';

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

B. Merge gebruiken om UPDATE- en DELETE-bewerkingen uit te voeren in een tabel in één instructie

In het volgende voorbeeld wordt MERGE gebruikt om de tabel in de ProductInventory voorbeelddatabase AdventureWorks2022 dagelijks bij te werken op basis van orders die in de SalesOrderDetail tabel worden verwerkt. De Quantity kolom van de ProductInventory tabel wordt bijgewerkt door het aantal orders dat elke dag voor elk product in de SalesOrderDetail tabel wordt geplaatst, af te trekken. Als het aantal orders voor een product het voorraadniveau van een product verlaagt tot 0 of minder, wordt de rij voor dat product uit de ProductInventory tabel verwijderd.

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';

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();
GO

EXECUTE Production.usp_UpdateInventory '20030501';

C. Merge gebruiken om UPDATE- en INSERT-bewerkingen uit te voeren op een doeltabel met behulp van een afgeleide brontabel

In het volgende voorbeeld wordt MERGE gebruikt om de tabel in de SalesReason AdventureWorks2022-database te wijzigen door rijen bij te werken of in te voegen.

-- 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;

MERGE INTO Sales.SalesReason AS tgt
USING (
    SELECT 'Recommendation', 'Other'
    UNION ALL    
    SELECT 'Review', 'Marketing'
    UNION ALL
    SELECT '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);

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

-- 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

Related content