MERGE (Transact-SQL)

Gäller för:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics (endast dedikerad SQL-pool)SQL-databas i Förhandsversion av Microsoft FabricLager i Microsoft Fabric

Instruktionen MERGE kör åtgärder för att infoga, uppdatera eller ta bort i en måltabell från resultatet av en koppling med en källtabell. Synkronisera till exempel två tabeller genom att infoga, uppdatera eller ta bort rader i en tabell baserat på skillnader som finns i den andra tabellen.

Den här artikeln innehåller olika syntax, argument, kommentarer, behörigheter och exempel baserat på den valda produktversionen. Välj önskad produktversion i listrutan version.

Transact-SQL syntaxkonventioner

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

MED <common_table_expression>

Anger den tillfälliga namngivna resultatuppsättningen eller vyn, även kallat common table expression, som definieras inom omfånget för -instruktionen MERGE . Resultatuppsättningen härleds från en enkel fråga och refereras av -instruktionen MERGE . Mer information finns i WITH common_table_expression (Transact-SQL).

database_name

Namnet på databasen där target_table finns.

schema_name

Namnet på schemat som target_table tillhör.

target_table

Tabellen eller vyn som dataraderna från <table_source> matchas mot baserat på <clause_search_condition>. target_table är målet för alla infognings-, uppdaterings- eller borttagningsåtgärder som anges av instruktionens WHENMERGE satser.

Om target_table är en vy måste alla åtgärder mot den uppfylla villkoren för uppdatering av vyer. Mer information finns i Ändra data via en vy.

target_table kan inte vara en fjärrtabell. target_table kan inte ha några definierade regler. target_table kan inte vara en minnesoptimerad tabell.

Tips kan anges som en <merge_hint>.

[ AS ] table_alias

Ett alternativt namn för att referera till en tabell för target_table.

ANVÄNDA <table_source>

Anger den datakälla som matchas med dataraderna i target_table baserat på <merge_search_condition>. Resultatet av den här matchningen avgör vilka åtgärder som ska utföras av instruktionens WHENMERGE satser. <table_source> kan vara en fjärrtabell eller en härledd tabell som har åtkomst till fjärrtabeller.

[ AS ] table_alias

Ett alternativt namn som refererar till en tabell för table_source.

Mer information om syntaxen och argumenten för den här satsen finns i FROM (Transact-SQL).

PÅ <merge_search_condition>

Anger de villkor som <table_source> ansluter till target_table för att avgöra var de matchar.

NÄR DEN MATCHAS <MERGE_MATCHED>

Anger att alla rader i *target_table, som matchar raderna som returneras av <table_source> ON <merge_search_condition>och uppfyller eventuella ytterligare sökvillkor, antingen uppdateras eller tas bort enligt <merge_matched> satsen.

Instruktionen MERGE kan ha högst två WHEN MATCHED satser. Om två satser anges måste den första satsen åtföljas av en AND<search_condition> -sats. För en viss rad tillämpas den andra WHEN MATCHED satsen endast om den första inte är det. Om det finns två WHEN MATCHED satser måste en ange en UPDATE åtgärd och en måste ange en DELETE åtgärd. När UPDATE anges i <merge_matched> -satsen och mer än en rad <table_source> matchar en rad i target_table baserat på <merge_search_condition>returnerar SQL Server ett fel. Instruktionen MERGE kan inte uppdatera samma rad mer än en gång eller uppdatera och ta bort samma rad.

NÄR DEN INTE MATCHAS [ EFTER MÅL ] <MERGE_NOT_MATCHED>

Anger att en rad infogas i target_table för varje rad som returneras av <table_source> ON <merge_search_condition> som inte matchar en rad i target_table, men uppfyller ytterligare ett sökvillkor, om det finns. Värdena som ska infogas anges av <merge_not_matched> -satsen. -instruktionen MERGE kan bara ha en WHEN NOT MATCHED [ BY TARGET ] sats.

NÄR DEN INTE MATCHAS AV KÄLLAN <MERGE_MATCHED>

Anger att alla rader i *target_table, som inte matchar raderna som returneras av <table_source> ON <merge_search_condition>, och som uppfyller eventuella ytterligare sökvillkor, uppdateras eller tas bort enligt <merge_matched> satsen.

Instruktionen MERGE kan ha högst två WHEN NOT MATCHED BY SOURCE satser. Om två satser anges måste den första satsen åtföljas av en AND<clause_search_condition> -sats. För en viss rad tillämpas den andra WHEN NOT MATCHED BY SOURCE satsen endast om den första inte är det. Om det finns två WHEN NOT MATCHED BY SOURCE satser måste en ange en UPDATE åtgärd och en måste ange en DELETE åtgärd. Endast kolumner från måltabellen kan refereras till i <clause_search_condition>.

När inga rader returneras av <table_source>kan kolumner i källtabellen inte nås. Om uppdaterings- eller borttagningsåtgärden <merge_matched> som anges i satsen refererar till kolumner i källtabellen returneras fel 207 (ogiltigt kolumnnamn). Satsen WHEN NOT MATCHED BY SOURCE THEN UPDATE SET TargetTable.Col1 = SourceTable.Col1 kan till exempel göra att -instruktionen misslyckas eftersom Col1 det inte går att komma åt den i källtabellen.

OCH <clause_search_condition>

Anger ett giltigt sökvillkor. Mer information finns i Sökvillkor (Transact-SQL).

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

Anger att optimerartips används för att anpassa hur databasmotorn bearbetar -instruktionen. Mer information finns i Frågetips (Transact-SQL).

<merge_matched>

Anger den uppdaterings- eller borttagningsåtgärd som tillämpas på alla rader i target_table som inte matchar raderna som returneras av <table_source> ON <merge_search_condition>och som uppfyller eventuella ytterligare sökvillkor.

UPPDATERINGSUPPSÄTTNING <set_clause>

Anger listan med kolumn- eller variabelnamn som ska uppdateras i måltabellen och de värden som de ska uppdateras med.

Mer information om argumenten i den här satsen finns i UPDATE (Transact-SQL). Det går inte att ställa in en variabel på samma värde som en kolumn.

DELETE

Anger att raderna som matchar raderna i target_table tas bort.

<merge_not_matched>

Anger de värden som ska infogas i måltabellen.

( column_list )

En lista över en eller flera kolumner i måltabellen där data ska infogas. Kolumner måste anges som ett endelsnamn, annars misslyckas instruktionen MERGE . column_list måste omges av parenteser och avgränsas med kommatecken.

VALUES ( values_list )

En kommaavgränsad lista över konstanter, variabler eller uttryck som returnerar värden som ska infogas i måltabellen. Uttryck får inte innehålla en EXECUTE -instruktion.

<search_condition>

Anger sökvillkoren för att ange <merge_search_condition> eller <clause_search_condition>. Mer information om argumenten för den här satsen finns i Sökvillkor (Transact-SQL).

Remarks

Det villkorsstyrda beteende som beskrivs för -instruktionen MERGE fungerar bäst när de två tabellerna har en komplex blandning av matchande egenskaper. Du kan till exempel infoga en rad om den inte finns eller uppdatera en rad om den matchar. När du bara uppdaterar en tabell baserat på raderna i en annan tabell kan du förbättra prestanda och skalbarhet med INSERT, UPDATEoch DELETE -instruktioner. Till exempel:

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

Minst en av de tre MATCHED satserna måste anges, men de kan anges i valfri ordning. En variabel kan inte uppdateras mer än en gång i samma MATCHED sats.

Alla infognings-, uppdaterings- eller borttagningsåtgärder som anges i måltabellen av -instruktionen MERGE begränsas av eventuella begränsningar som definierats för den, inklusive eventuella sammanhängande begränsningar för referensintegritet. Om IGNORE_DUP_KEY är ON för några unika index i måltabellen MERGE ignorerar du den här inställningen.

Instruktionen MERGE kräver ett semikolon (;) som en instruktionsavgränsare. Fel 10713 utlöses när en MERGE instruktion körs utan avslutaren.

När det används efter MERGEreturnerar @@ROWCOUNT (Transact-SQL) det totala antalet rader som infogats, uppdaterats och tagits bort till klienten.

MERGE är ett fullständigt reserverat nyckelord när databasens kompatibilitetsnivå är inställd på 100 eller högre. -instruktionen MERGE är tillgänglig under både 90 och 100 databaskompatibilitetsnivåer. Nyckelordet är dock inte helt reserverat när databasens kompatibilitetsnivå är inställd på 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

Utlösarimplementering

För varje infognings-, uppdaterings- eller borttagningsåtgärd som anges i -instruktionen MERGE utlöser SQL Server motsvarande AFTER utlösare som definierats i måltabellen, men garanterar inte vilken åtgärd som utlöser utlösare först eller sist. Utlösare som definierats för samma åtgärd respekterar den ordning som du anger. Mer information om hur du anger utlösaravfyrningsordning finns i Ange första och sista utlösare.

Om måltabellen har en aktiverad INSTEAD OF-utlösare som definierats för en infognings-, uppdaterings- eller borttagningsåtgärd som utförs av en MERGE -instruktion, måste den ha en aktiverad INSTEAD OF-utlösare för alla åtgärder som anges i -instruktionen MERGE .

Om några OFUPDATE- INSTEAD eller INSTEAD OF-utlösare DELETE definieras på target_table körs inte uppdaterings- eller borttagningsåtgärderna. I stället utlöses utlöses och de infogade och borttagna tabellerna fylls sedan i därefter.

Om några OF-utlösare INSTEADINSERT definieras på target_table utförs inte infogningsåtgärden. I stället fylls tabellen i.

Permissions

Kräver SELECT behörighet för källtabellen och INSERT, UPDATEeller DELETE behörigheter för måltabellen. Mer information finns i avsnittet Behörigheter i artiklarna SELECT (Transact-SQL), INSERT (Transact-SQL), UPDATE (Transact-SQL)och DELETE (Transact-SQL).

Metodtips för index

Med hjälp av -instruktionen MERGE kan du ersätta de enskilda DML-uttrycken med en enda instruktion. Detta kan förbättra frågeprestanda eftersom åtgärderna utförs i en enda instruktion, vilket minimerar antalet gånger data i käll- och måltabellerna bearbetas. Prestandavinster beror dock på att rätt index, kopplingar och andra överväganden finns på plats.

För att förbättra prestanda för -instruktionen MERGE rekommenderar vi följande indexriktlinjer:

• Skapa index för att underlätta kopplingen mellan källan och målet för MERGE:
  • Skapa ett index för kopplingskolumnerna i källtabellen som har nycklar som täcker kopplingslogik till måltabellen. Det bör om möjligt vara unikt.
  • Skapa också ett index för kopplingskolumnerna i måltabellen. Det bör om möjligt vara ett unikt grupperat index.
  • Dessa två index säkerställer att data i tabellerna sorteras, och unikhet underlättar prestanda för jämförelsen. Frågeprestanda förbättras eftersom frågeoptimeraren inte behöver utföra extra valideringsbearbetning för att hitta och uppdatera dubblettrader och ytterligare sorteringsåtgärder behövs inte.
• Undvik tabeller med någon form av columnstore-index som mål MERGE för -instruktioner. Precis som med alla UPDATEs kan du hitta bättre prestanda med columnstore-index genom att uppdatera en mellanlagrad radlagringstabell och sedan utföra en batchbaserad DELETE och INSERT, i stället för en UPDATE eller MERGE.

Samtidighetsöverväganden för MERGE

När det gäller låsning MERGE skiljer sig från diskreta, på varandra följande INSERT, UPDATEoch DELETE -instruktioner. MERGE kör INSERTfortfarande , UPDATEoch DELETE åtgärder, men använder olika låsmekanismer. Det kan vara mer effektivt att skriva diskreta INSERT, UPDATEoch DELETE -instruktioner för vissa programbehov. I stor skala MERGE kan det medföra komplicerade samtidighetsproblem eller kräva avancerad felsökning. Därför planerar du att noggrant testa alla MERGE instruktioner innan du distribuerar till produktion.

MERGE -instruktioner är en lämplig ersättning för diskreta INSERT, UPDATEoch DELETE åtgärder i (men inte begränsat till) följande scenarier:

• ETL-åtgärder som omfattar stora radantal körs under en tid då andra samtidiga åtgärder inte är* förväntade. När stor samtidighet förväntas kan separat INSERT, UPDATEoch DELETE logik fungera bättre, med mindre blockering, än en MERGE -instruktion.
• Komplexa åtgärder som omfattar små radantal och transaktioner som sannolikt inte kommer att köras under längre tid.
• Komplexa åtgärder som involverar användartabeller där index kan utformas för att säkerställa optimala körningsplaner, undvika tabellgenomsökningar och sökningar till förmån för indexgenomsökningar eller - helst - indexsökningar.

Andra överväganden för samtidighet:

• I vissa scenarier där unika nycklar förväntas både infogas och uppdateras av MERGE, anger du HOLDLOCK kommer att förhindra unika nyckelöverträdelser. HOLDLOCK är en synonym för SERIALIZABLE transaktionsisoleringsnivån, vilket inte tillåter att andra samtidiga transaktioner ändrar data som transaktionen har läst. SERIALIZABLE är den säkraste isoleringsnivån men ger minst samtidighet med andra transaktioner som behåller lås på dataintervall för att förhindra att fantomrader infogas eller uppdateras medan läsningar pågår. Mer information om HOLDLOCKfinns i Tabelltips och ANGE TRANSAKTIONSISOLERINGSNIVÅ (Transact-SQL).

Metodtips för JOIN

För att förbättra prestanda för -instruktionen MERGE och se till att korrekta resultat erhålls rekommenderar vi följande kopplingsriktlinjer:

• Ange endast sökvillkor i - ON <merge_search_condition> satsen som avgör villkoren för matchning av data i käll- och måltabellerna. Det vill: ange endast kolumner från måltabellen som jämförs med motsvarande kolumner i källtabellen.
• Ta inte med jämförelser med andra värden, till exempel en konstant.

Om du vill filtrera bort rader från käll- eller måltabellerna använder du någon av följande metoder.

• Ange sökvillkoret för radfiltrering i lämplig WHEN sats. Till exempel: WHEN NOT MATCHED AND S.EmployeeName LIKE 'S%' THEN INSERT....
• Definiera en vy på källan eller målet som returnerar de filtrerade raderna och refererar till vyn som käll- eller måltabell. Om vyn definieras i måltabellen måste alla åtgärder mot den uppfylla villkoren för uppdatering av vyer. Mer information om hur du uppdaterar data med hjälp av en vy finns i Ändra data via en vy.
• WITH <common table expression> Använd -satsen för att filtrera bort rader från käll- eller måltabellerna. Den här metoden liknar att ange ytterligare sökvillkor i ON -satsen och kan ge felaktiga resultat. Vi rekommenderar att du undviker att använda den här metoden eller testa noggrant innan du implementerar den.

Kopplingsåtgärden i -instruktionen MERGE optimeras på samma sätt som en koppling i en SELECT -instruktion. När SQL Server bearbetar anslutning väljer frågeoptimeraren den mest effektiva metoden (av flera möjligheter) för bearbetning av kopplingen. När källan och målet är av liknande storlek och de indexriktlinjer som beskrivs tidigare tillämpas på käll- och måltabellerna är en kopplingsoperator den mest effektiva frågeplanen. Det beror på att båda tabellerna genomsöks en gång och det inte finns något behov av att sortera data. När källan är mindre än måltabellen är en kapslad loopoperator att föredra.

Du kan framtvinga användningen av en specifik koppling genom att OPTION (<query_hint>) ange -satsen i -instruktionen MERGE . Vi rekommenderar att du inte använder hash-kopplingen som ett frågetips för MERGE instruktioner eftersom den här kopplingstypen inte använder index.

Metodtips för parameterisering

Om en SELECT, INSERT, UPDATE, eller DELETE -instruktion körs utan parametrar kan SQL Server-frågeoptimeraren välja att parameterisera instruktionen internt. Det innebär att alla literalvärden som finns i frågan ersätts med parametrar. Instruktionen INSERT dbo.MyTable (Col1, Col2) VALUES (1, 10)kan till exempel implementeras internt som INSERT dbo.MyTable (Col1, Col2) VALUES (@p1, @p2). Den här processen, som kallas enkel parameterisering, ökar relationsmotorns förmåga att matcha nya SQL-instruktioner med befintliga, tidigare kompilerade körningsplaner. Frågeprestanda kan förbättras eftersom frekvensen för frågekompileringar och omkompileringar minskar. Frågeoptimeraren tillämpar inte den enkla parameteriseringsprocessen på MERGE -instruktioner. Därför MERGE kan det hända att instruktioner som innehåller literalvärden inte utförs och enskilda INSERT, UPDATE, eller DELETE -instruktioner eftersom en ny plan kompileras varje gång instruktionen MERGE körs.

För att förbättra frågeprestanda rekommenderar vi följande riktlinjer för parameterisering:

• Parametrisera alla literalvärden i ON <merge_search_condition> -satsen och i instruktionens MERGEWHEN satser. Du kan till exempel införliva -instruktionen MERGE i en lagrad procedur som ersätter literalvärdena med lämpliga indataparametrar.
• Om du inte kan parametrisera -instruktionen skapar du en planguide av typen TEMPLATE och anger frågetipset PARAMETERIZATION FORCED i planguiden. Mer information finns i Ange beteende för frågeparameterisering med hjälp av planguider.
• Om MERGE instruktioner körs ofta på databasen kan du överväga att ange PARAMETERIZATION alternativet för databasen till FORCED. Var försiktig när du anger det här alternativet. Alternativet PARAMETERIZATION är en databasnivåinställning och påverkar hur alla frågor mot databasen bearbetas. Mer information finns i Tvingad parameterisering.
• Som ett nyare och enklare alternativ till planguider bör du överväga en liknande strategi med Query Store-tips. Mer information finns i Query Store-tips.

Metodtips för TOP-satser

I -instruktionen MERGETOP anger satsen antalet eller procentandelen rader som påverkas efter att källtabellen och måltabellen har anslutits, och efter att rader som inte kvalificerar sig för en åtgärd för att infoga, uppdatera eller ta bort har tagits bort. TOP Satsen minskar ytterligare antalet kopplade rader till det angivna värdet och åtgärderna infoga, uppdatera eller ta bort tillämpas på de återstående kopplade raderna på ett oordnat sätt. Det betyder att det inte finns någon ordning i vilken raderna distribueras mellan de åtgärder som definieras i satserna WHEN . Om du till exempel anger TOP (10) påverkar 10 rader. Av dessa rader kan 7 uppdateras och 3 infogas, eller så kan 1 tas bort, 5 uppdateras och 4 infogas och så vidare.

Det är vanligt att använda TOP -satsen för att utföra DML-åtgärder (datamanipuleringsspråk) på en stor tabell i batchar. När du använder TOP -satsen i -instruktionen för det här ändamålet MERGE är det viktigt att förstå följande konsekvenser.

• I/O-prestanda kan påverkas.

  Instruktionen MERGE utför en fullständig tabellgenomsökning av både käll- och måltabellerna. Genom att dela upp åtgärden i batchar minskar antalet skrivåtgärder som utförs per batch. Varje batch utför dock en fullständig tabellgenomsökning av käll- och måltabellerna. Den resulterande läsaktiviteten kan påverka frågans prestanda och annan samtidig aktivitet i tabellerna.

• Felaktiga resultat kan inträffa.

  Det är viktigt att se till att alla efterföljande batchar riktar in sig på nya rader eller oönskade beteenden, till exempel att felaktigt infoga dubblettrader i måltabellen kan inträffa. Detta kan inträffa när källtabellen innehåller en rad som inte fanns i en målbatch men som fanns i den övergripande måltabellen. Så här säkerställer du rätt resultat:

  • ON Använd -satsen för att avgöra vilka källrader som påverkar befintliga målrader och vilka som verkligen är nya.
  • Använd ytterligare ett villkor i WHEN MATCHED -satsen för att avgöra om målraden redan har uppdaterats av en tidigare batch.
  • Använd ytterligare ett villkor i -satsen och SET logiken WHEN MATCHED för att verifiera att samma rad inte kan uppdateras två gånger.

TOP Eftersom satsen endast tillämpas efter att dessa satser har tillämpats infogar varje körning antingen en verkligt omatchad rad eller uppdaterar en befintlig rad.

Metodtips för massinläsning

-instruktionen MERGE kan användas för att effektivt massinläsa data från en källdatafil till en måltabell genom att OPENROWSET(BULK...) ange -satsen som tabellkälla. På så sätt bearbetas hela filen i en enda batch.

För att förbättra prestanda för masssammanslagningsprocessen rekommenderar vi följande riktlinjer:

• Skapa ett grupperat index på kopplingskolumnerna i måltabellen.

• Inaktivera andra icke-unika, icke-illustrerade index i måltabellen under massinläsningen MERGEoch aktivera dem efteråt. Detta är vanligt och användbart för nattliga massdataåtgärder.

• Använd tipsen ORDER och UNIQUE i OPENROWSET(BULK...) -satsen för att ange hur källdatafilen sorteras.

  Som standard förutsätter massåtgärden att datafilen är osorterad. Därför är det viktigt att källdata sorteras enligt det klustrade indexet i måltabellen och att tipset ORDER används för att ange ordningen så att frågeoptimeraren kan generera en effektivare frågeplan. Tips verifieras vid körning. Om dataströmmen inte överensstämmer med de angivna tipsen utlöses ett fel.

Dessa riktlinjer säkerställer att kopplingsnycklarna är unika och att sorteringsordningen för data i källfilen matchar måltabellen. Frågeprestanda förbättras eftersom ytterligare sorteringsåtgärder inte behövs och onödiga datakopior inte krävs.

Mäta och diagnostisera MERGE-prestanda

Följande funktioner är tillgängliga för att hjälpa dig att mäta och diagnostisera prestanda MERGE för instruktioner.

• Använd sammanslagningsräknaren i vyn sys.dm_exec_query_optimizer_info dynamisk hantering för att returnera antalet frågeoptimeringar som gäller för MERGE -instruktioner.
• merge_action_type Använd attributet i vyn sys.dm_exec_plan_attributes dynamisk hantering för att returnera den typ av utlösarkörningsplan som används som resultat av en MERGE instruktion.
• Använd en extended events-session för att samla in felsökningsdata för -instruktionen MERGE på samma sätt som för andra DML-instruktioner (datamanipuleringsspråk). Mer information om översikt över utökade händelserfinns i snabbstarten : Utökade händelser och Använd SSMS XEvent Profiler.

Examples

A. Använd MERGE för att utföra INSERT- och UPDATE-åtgärder i en tabell i en enda instruktion

Ett vanligt scenario är att uppdatera en eller flera kolumner i en tabell om det finns en matchande rad. Eller infoga data som en ny rad om det inte finns någon matchande rad. Du gör vanligtvis något av scenarierna genom att skicka parametrar till en lagrad procedur som innehåller lämpliga UPDATE instruktioner och INSERT instruktioner. Med -instruktionen MERGE kan du utföra båda uppgifterna i en enda instruktion. I följande exempel visas en lagrad procedur i databasen AdventureWorks2022 som innehåller både en INSERT -instruktion och en UPDATE -instruktion. Proceduren ändras sedan för att köra motsvarande åtgärder med hjälp av en enda MERGE instruktion.

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. Använd MERGE för att utföra uppdaterings- och DELETE-åtgärder i en tabell i en enda instruktion

I följande exempel används MERGE för att uppdatera ProductInventory tabellen i AdventureWorks2022-exempeldatabasen, dagligen, baserat på beställningar som bearbetas i SalesOrderDetail tabellen. Kolumnen Quantity i ProductInventory tabellen uppdateras genom att subtrahera antalet beställningar som görs varje dag för varje produkt i SalesOrderDetail tabellen. Om antalet beställningar för en produkt minskar lagernivån för en produkt till 0 eller mindre tas raden för produkten bort från ProductInventory tabellen.

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. Använd MERGE för att utföra UPPDATERINGs- och INSERT-åtgärder i en måltabell med hjälp av en härledd källtabell

I följande exempel används MERGE för att ändra SalesReason tabellen i databasen AdventureWorks2022 genom att antingen uppdatera eller infoga rader.

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

Relaterat innehåll

• SELECT (Transact-SQL)
• INSERT (Transact-SQL)
• UPDATE (Transact-SQL)
• TA BORT (Transact-SQL)
• OUTPUT-sats (Transact-SQL)
• MERGE i Integration Services-paket
• FROM-klausul plus JOIN, APPLY, PIVOT (Transact-SQL)
• Tabellvärdekonstruktor (Transact-SQL)