MERGE (Transact-SQL)

Si applica a:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics (solo pool SQL dedicato)Database SQL in Microsoft FabricWarehouse in Microsoft Fabric

L'istruzione MERGE esegue operazioni di inserimento, aggiornamento o eliminazione in una tabella di destinazione dai risultati di un join con una tabella di origine. Sincronizzare, ad esempio, due tabelle inserendo, aggiornando o eliminando righe in una tabella in base alle differenze trovate nell'altra tabella.

Questo articolo fornisce sintassi, argomenti, osservazioni, autorizzazioni ed esempi diversi in base alla versione del prodotto selezionata. Selezionare la versione desiderata del prodotto dall'elenco a discesa versione.

Convenzioni relative alla sintassi Transact-SQL

Syntax

Sintassi per SQL Server e database SQL di Azure:

[ 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

CON <common_table_expression>

Specifica il set di risultati o la vista denominata temporanea, nota anche come espressione di tabella comune, definita nell'ambito dell'istruzione MERGE . Il set di risultati deriva da una query semplice e fa riferimento all'istruzione MERGE . Per altre informazioni, vedere WITH common_table_expression (Transact-SQL).

TOP (expression) [ PERCENT ]

Specifica il numero o la percentuale di righe interessate. Il valore di expression può essere un numero o una percentuale delle righe. Le righe a cui si fa riferimento nell'espressione TOP non sono disposte in alcun ordine. Per altre informazioni, vedere TOP (Transact-SQL).

La TOP clausola si applica dopo la rimozione dell'intera tabella di origine e dell'intero join della tabella di destinazione e delle righe unite non idonee per un'azione di inserimento, aggiornamento o eliminazione. La TOP clausola riduce ulteriormente il numero di righe unite in join al valore specificato. Queste azioni (inserimento, aggiornamento o eliminazione) si applicano alle righe unite rimanenti in modo non ordinato. Ciò significa che non esiste un ordine in cui le righe vengono distribuite tra le azioni definite nelle WHEN clausole . Ad esempio, la specifica TOP di (10) influisce su 10 righe. Di queste righe, 7 potrebbero essere aggiornate e 3 inserite oppure 1 potrebbero essere eliminate, 5 aggiornate e 4 inserite e così via.

Senza filtri sulla tabella di origine, l'istruzione MERGE potrebbe eseguire un'analisi di tabella o un'analisi dell'indice cluster nella tabella di origine, nonché un'analisi dell'indice cluster o un'analisi dell'indice cluster della tabella di destinazione. Di conseguenza, le prestazioni di I/O sono talvolta influenzate anche quando si usa la TOP clausola per modificare una tabella di grandi dimensioni creando più batch. In questo scenario è importante assicurarsi che tutti i batch successivi abbiano come destinazione nuove righe.

database_name

Nome del database in cui si trova target_table.

schema_name

Nome dello schema a cui appartiene la tabella target_table.

target_table

Tabella o vista rispetto alla quale vengono associate le righe di dati di <table_source> in base a <clause_search_condition>. target_table è la destinazione di qualsiasi operazione di inserimento, aggiornamento o eliminazione specificata dalle WHEN clausole dell'istruzione MERGE .

Se target_table è una vista, qualsiasi azione eseguita su di essa deve soddisfare le condizioni per l'aggiornamento delle viste. Per altre informazioni, vedere Modificare i dati tramite una vista.

target_table non può essere una tabella remota. target_table non può avere regole definite. target_table non può essere una tabella ottimizzata per la memoria.

Gli hint possono essere specificati come .<merge_hint>

[AS] table_alias

Un nome alternativo per fare riferimento alla tabella target_table.

USO <table_source>

Specifica l'origine dati corrispondente alle righe di dati in target_table in base a <merge_search_condition>. Il risultato di questa corrispondenza determina le azioni da eseguire dalle WHEN clausole dell'istruzione MERGE . <table_source> può essere una tabella remota o una tabella derivata con accesso a tabelle remote.

<table_source>può essere una tabella derivata che usa il costruttore di valori di tabella Transact-SQL per costruire una tabella specificando più righe.

[AS] table_alias

Un nome alternativo per fare riferimento alla tabella table_source.

Per altre informazioni sulla sintassi e gli argomenti di questa clausola, vedere FROM (Transact-SQL).

IL <merge_search_condition>

Specifica le condizioni in base alle quali viene creato il join tra <table_source> e target_table per stabilire i punti di corrispondenza.

Caution

È importante specificare solo le colonne della tabella di destinazione da usare ai fini della corrispondenza, ovvero specificare colonne della tabella di destinazione confrontate con quella corrispondente della tabella di origine. Non tentare di migliorare le prestazioni delle query filtrando le righe nella tabella di destinazione nella ON clausola , ad esempio specificando AND NOT target_table.column_x = value. In questo modo è possibile restituire risultati imprevisti e non corretti.

WHEN MATCHED THEN <MERGE_MATCHED>

Specifica che tutte le righe di *target_table, che corrispondono alle righe restituite da <table_source> ON <merge_search_condition>e soddisfano eventuali condizioni di ricerca aggiuntive, vengono aggiornate o eliminate in base alla <merge_matched> clausola .

L'istruzione MERGE può avere al massimo due WHEN MATCHED clausole. Se vengono specificate due clausole, la prima clausola deve essere accompagnata da una AND<search_condition> clausola . Per una determinata riga, la seconda WHEN MATCHED clausola viene applicata solo se la prima non è. Se sono presenti due WHEN MATCHED clausole, è necessario specificare un'azione UPDATE e una deve specificare un'azione DELETE . Quando UPDATE viene specificato nella <merge_matched> clausola e più righe di <table_source> corrisponde a una riga in target_table in <merge_search_condition>base a , SQL Server restituisce un errore. L'istruzione MERGE non può aggiornare più volte la stessa riga oppure aggiornare ed eliminare la stessa riga.

QUANDO NON CORRISPONDE [ BY TARGET ] THEN <MERGE_NOT_MATCHED>

Specifica che una riga viene inserita in target_table per ogni riga restituita da <table_source> ON <merge_search_condition> che non corrisponde a una riga in target_table, ma soddisfa una condizione di ricerca aggiuntiva, se presente. I valori da inserire vengono specificati dalla clausola <merge_not_matched>. L'istruzione MERGE può avere una WHEN NOT MATCHED [ BY TARGET ] sola clausola.

QUANDO NON CORRISPONDE ALL'ORIGINE, <MERGE_MATCHED>

Specifica che tutte le righe di *target_table, che non corrispondono alle righe restituite da <table_source> ON <merge_search_condition>e che soddisfano eventuali condizioni di ricerca aggiuntive, vengono aggiornate o eliminate in base alla <merge_matched> clausola .

L'istruzione MERGE può avere al massimo due WHEN NOT MATCHED BY SOURCE clausole. Se vengono specificate due clausole, la prima clausola deve essere accompagnata da una AND<clause_search_condition> clausola . Per una determinata riga, la seconda WHEN NOT MATCHED BY SOURCE clausola viene applicata solo se la prima non è. Se sono presenti due WHEN NOT MATCHED BY SOURCE clausole, è necessario specificare un'azione UPDATE e una deve specificare un'azione DELETE . <clause_search_condition> può fare riferimento solo a colonne della tabella di destinazione.

Se da <table_source> non viene restituita alcuna riga, non è possibile accedere alle colonne della tabella di origine. Se l'azione di aggiornamento o eliminazione specificata nella clausola <merge_matched> fa riferimento a colonne della tabella di origine, viene restituito l'errore 207 (Nome di colonna non valido). Ad esempio, la clausola WHEN NOT MATCHED BY SOURCE THEN UPDATE SET TargetTable.Col1 = SourceTable.Col1 può causare l'esito negativo dell'istruzione perché Col1 nella tabella di origine non è accessibile.

E clause_search_condition <>

Specifica qualsiasi condizione di ricerca valida. Per altre informazioni, vedere Condizione di ricerca (Transact-SQL).

<table_hint_limited>

Specifica uno o più hint di tabella da applicare nella tabella di destinazione per ognuna delle azioni di inserimento, aggiornamento o eliminazione eseguite dall'istruzione MERGE . La WITH parola chiave e le parentesi sono obbligatorie.

NOLOCK e READUNCOMMITTED non sono consentiti. Per altre informazioni sugli hint di tabella, vedere Hint di tabella (Transact-SQL).

La specifica dell'hint TABLOCK in una tabella che rappresenta la destinazione di un'istruzione INSERT ha lo stesso effetto di specificare l'hint TABLOCKX . poiché determina l'acquisizione di un blocco esclusivo sulla tabella. Quando viene specificato, FORCESEEK viene applicato all'istanza implicita della tabella di destinazione unita in join con la tabella di origine.

Caution

L'impostazione READPAST di con WHEN NOT MATCHED [ BY TARGET ] THEN INSERT può comportare INSERT operazioni che violano UNIQUE i vincoli.

INDEX ( index_val [ ,... n ] )

Specifica il nome o l'ID di uno o più indici della tabella di destinazione per eseguire un join implicito con la tabella di origine. Per ulteriori informazioni, vedere Hint di tabella (Transact-SQL).

<output_clause>

Restituisce una riga per ogni riga in target_table aggiornata, inserita o eliminata, senza alcun ordine specifico. $action può essere specificato nella clausola di output. $action è una colonna di tipo nvarchar(10) che restituisce uno dei tre valori per ogni riga: INSERT, UPDATEo DELETE, in base all'azione eseguita su tale riga. La OUTPUT clausola è il modo consigliato per eseguire query o contare righe interessate da un oggetto MERGE. Per altre informazioni sugli argomenti e sul comportamento di questa clausola, vedere clausola OUTPUT (Transact-SQL).

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

Specifica che vengono utilizzati hint di ottimizzazione per personalizzare il modo in cui il Motore di database elabora l'istruzione. Per altre informazioni, vedere Hint di query (Transact-SQL).

<merge_matched>

Specifica l'azione di aggiornamento o eliminazione applicata a tutte le righe di target_table che non corrispondono alle righe restituite da <table_source> ON <merge_search_condition>e che soddisfano eventuali condizioni di ricerca aggiuntive.

AGGIORNAMENTO IMPOSTATO <set_clause>

Specifica l'elenco di colonne o di nomi di variabile da aggiornare nella tabella di destinazione e i valori in base ai quali eseguire l'aggiornamento.

Per altre informazioni sugli argomenti di questa clausola, vedere UPDATE (Transact-SQL). L'impostazione di una variabile sullo stesso valore di una colonna non è supportata.

DELETE

Specifica che le righe corrispondenti alle righe di target_table vengono eliminate.

<merge_not_matched>

Specifica i valori da inserire nella tabella di destinazione.

( column_list )

Elenco di una o più colonne della tabella di destinazione in cui inserire i dati. Le colonne devono essere specificate come nome a singola parte oppure l'istruzione MERGE ha esito negativo. Il valore di column_list deve essere racchiuso tra parentesi e delimitato da virgole.

VALUES ( values_list)

Elenco di costanti, variabili o espressioni separate da virgole, che restituiscono valori da inserire nella tabella di destinazione. Le espressioni non possono contenere un'istruzione EXECUTE .

VALORI PREDEFINITI

Forza l'immissione nella riga inserita dei valori predefiniti associati a ogni colonna.

Per altre informazioni su questa clausola, vedere INSERT (Transact-SQL).

<search_condition>

Definisce le condizioni di ricerca per specificare <merge_search_condition> o <clause_search_condition>. Per altre informazioni sugli argomenti per questa clausola, vedere Condizione di ricerca (Transact-SQL).

<modello di ricerca grafico>

Specifica il modello di corrispondenza del grafico. Per altre informazioni sugli argomenti per questa clausola, vedere MATCH (Transact-SQL).

Remarks

Il comportamento condizionale descritto per l'istruzione MERGE funziona meglio quando le due tabelle hanno una combinazione complessa di caratteristiche corrispondenti. Ad esempio, inserire una riga se non esiste o aggiornare una riga se corrisponde. Quando si aggiorna semplicemente una tabella in base alle righe di un'altra tabella, migliorare le prestazioni e la scalabilità con INSERTle istruzioni , UPDATEe DELETE . Per esempio:

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

È necessario specificare almeno una delle tre MATCHED clausole, ma possono essere specificate in qualsiasi ordine. Una variabile non può essere aggiornata più volte nella stessa MATCHED clausola.

Qualsiasi azione di inserimento, aggiornamento o eliminazione specificata nella tabella di destinazione dall'istruzione MERGE è limitata da tutti i vincoli definiti, inclusi eventuali vincoli di integrità referenziale a catena. Se IGNORE_DUP_KEY è ON per qualsiasi indice univoco nella tabella di destinazione, MERGE ignora questa impostazione.

L'istruzione MERGE richiede un punto e virgola (;) come carattere di terminazione dell'istruzione. L'errore 10713 viene generato quando un'istruzione MERGE viene eseguita senza il carattere di terminazione.

Se usato dopo MERGE, @@ROWCOUNT (Transact-SQL) restituisce il numero totale di righe inserite, aggiornate ed eliminate nel client.

MERGE è una parola chiave completamente riservata quando il livello di compatibilità del database è impostato su 100 o superiore. L'istruzione MERGE è disponibile sia 90100 con i livelli di compatibilità del database che con la parola chiave non è completamente riservata quando il livello di compatibilità del database è impostato su 90.

Caution

Non usare l'istruzione quando si usa la MERGEreplica ad aggiornamento in coda. Il MERGE trigger di aggiornamento in coda e non è compatibile. Sostituire l'istruzione MERGE con un'istruzione INSERT e UPDATE .

Implementazione del trigger

Per ogni azione di inserimento, aggiornamento o eliminazione specificata nell'istruzione MERGE , SQL Server attiva tutti i trigger corrispondenti AFTER definiti nella tabella di destinazione, ma non garantisce su quale azione attivare i trigger per primo o per ultimo. I trigger definiti per la stessa azione rispettano l'ordine specificato dall'utente. Per altre informazioni sull'impostazione dell'ordine di attivazione dei trigger, vedere Specifica dei primi e degli ultimi trigger.

Se nella tabella di destinazione è definito un trigger OF abilitato INSTEAD per un'azione di inserimento, aggiornamento o eliminazione eseguita da un'istruzione MERGE , deve disporre di un trigger OF abilitato INSTEAD per tutte le azioni specificate nell'istruzione MERGE .

INSTEAD Se in UPDATE sono definiti trigger OF INSTEAD o DELETE OF, le operazioni di aggiornamento o eliminazione non vengono eseguite. ma vengono attivati i trigger e le tabelle inserite ed eliminate vengono popolate di conseguenza.

Se in INSTEADINSERT sono definiti trigger OF, l'operazione di inserimento non viene eseguita. ma la tabella viene popolata di conseguenza.

Note

A differenza delle istruzioni , e separateINSERT, il numero di righe riflesse dall'interno UPDATE di un trigger potrebbe essere DELETE superiore. @@ROWCOUNT L'oggetto @@ROWCOUNT all'interno di qualsiasi AFTER trigger (indipendentemente dalle istruzioni di modifica dei dati acquisite dal trigger) rifletterà il numero totale di righe interessate da MERGE. Ad esempio, se un'istruzione inserisce una MERGE riga, aggiorna una riga ed elimina una riga, @@ROWCOUNT sarà tre per qualsiasi AFTER trigger, anche se il trigger viene dichiarato solo per INSERT le istruzioni.

Permissions

È necessaria SELECT l'autorizzazione per la tabella di origine e INSERTle autorizzazioni , UPDATEo DELETE per la tabella di destinazione. Per altre informazioni, vedere la sezione Autorizzazioni negli articoli SELECT (Transact-SQL), INSERT (Transact-SQL), UPDATE (Transact-SQL)e DELETE (Transact-SQL).

Procedure consigliate per gli indici

Usando l'istruzione MERGE , è possibile sostituire le singole istruzioni DML con una singola istruzione. In questo modo è possibile migliorare le prestazioni delle query poiché le operazioni vengono eseguite in un'unica istruzione, riducendo di conseguenza al minimo il numero di elaborazioni dei dati delle tabelle di origine e di destinazione. I vantaggi in termini di prestazioni dipendono tuttavia dalla disponibilità di join ed indici corretti e da altre considerazioni appropriate.

Per migliorare le prestazioni dell'istruzione, è consigliabile seguire le linee guida per l'indice MERGE seguenti:

• Creare indici per facilitare il join tra l'origine e la destinazione di MERGE:
  • Creare un indice nelle colonne di join nella tabella di origine contenente chiavi che coprono la logica di join alla tabella di destinazione. Se possibile, dovrà essere univoco.
  • Creare anche un indice nelle colonne di join nella tabella di destinazione. Se possibile, dovrà essere un indice cluster univoco.
  • Questi due indici assicurano l'ordinamento dei dati nelle tabelle, mentre l'univocità facilita le prestazioni del confronto. Le prestazioni delle query risultano migliorate poiché Query Optimizer non deve eseguire ulteriori elaborazioni di convalida per individuare e aggiornare righe duplicate né è necessario eseguire operazioni di ordinamento aggiuntive.
• Evitare tabelle con qualsiasi forma di indice columnstore come destinazione delle MERGE istruzioni. Come per qualsiasi UPDATEs, è possibile trovare prestazioni migliori con gli indici columnstore aggiornando una tabella rowstore di staging, quindi eseguendo un batch DELETE e INSERT, invece di o UPDATEMERGE.

Considerazioni sulla concorrenza per MERGE

In termini di blocco, MERGE è diverso dalle istruzioni discrete, consecutive INSERT, UPDATEe DELETE . MERGE esegue INSERTcomunque le operazioni , UPDATEe DELETE , tuttavia, usando meccanismi di blocco diversi. Potrebbe essere più efficiente scrivere istruzioni discrete INSERT, UPDATEe DELETE per alcune esigenze dell'applicazione. Su larga scala, MERGE potrebbe introdurre problemi di concorrenza complessi o richiedere la risoluzione dei problemi avanzata. Di conseguenza, pianificare il test accurato di qualsiasi MERGE istruzione prima della distribuzione nell'ambiente di produzione.

MERGE Le istruzioni sono una sostituzione adatta per le operazioni discrete INSERT, UPDATE, e DELETE in (ma non solo per) gli scenari seguenti:

• Operazioni ETL con un numero elevato di righe da eseguire in un intervallo di tempo in cui non sono previste altre operazioni simultanee. Quando è prevista una concorrenza elevata, la INSERTlogica separata , UPDATEe DELETE potrebbe offrire prestazioni migliori, con meno blocchi rispetto a un'istruzione MERGE .
• Operazioni complesse con un numero ridotto di righe e di transazioni la cui esecuzione non dovrebbe essere di lunga durata.
• Operazioni complesse con tabelle utente in cui gli indici possono essere progettati per assicurare piani di esecuzione ottimali, evitando analisi e ricerche nelle tabelle a favore di analisi o, idealmente, ricerche negli indici.

Altre considerazioni sulla concorrenza:

• In alcuni scenari in cui si prevede che le chiavi univoche vengano inserite e aggiornate da MERGE, specificando HOLDLOCK che impedirà le violazioni di chiave univoche. HOLDLOCK è un sinonimo del SERIALIZABLE livello di isolamento delle transazioni, che non consente ad altre transazioni simultanee di modificare i dati letti da questa transazione. SERIALIZABLE è il livello di isolamento più sicuro, ma fornisce la concorrenza minima con altre transazioni che conservano blocchi su intervalli di dati per impedire l'inserimento o l'aggiornamento di righe fantasma mentre le letture sono in corso. Per altre informazioni su HOLDLOCK, vedere Hint di tabella e SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

Procedure consigliate per l'operatore JOIN

Per migliorare le prestazioni dell'istruzione MERGE e assicurarsi che vengano ottenuti risultati corretti, è consigliabile seguire le linee guida di join seguenti:

• Specificare solo le condizioni di ricerca nella ON <merge_search_condition> clausola che determinano i criteri per la corrispondenza dei dati nelle tabelle di origine e di destinazione. ovvero specificare solo colonne della tabella di destinazione confrontate con quelle corrispondenti della tabella di origine.
• Non includere confronti con altri valori, ad esempio una costante.

Per filtrare le righe delle tabelle di origine o di destinazione, effettuare una delle operazioni indicate di seguito.

• Specificare la condizione di ricerca per il filtro delle righe nella clausola appropriata WHEN . Ad esempio, WHEN NOT MATCHED AND S.EmployeeName LIKE 'S%' THEN INSERT....
• Definire una vista sull'origine o sulla destinazione che restituisca le righe filtrate e faccia riferimento alla vista come la tabella di origine o di destinazione. Se la vista viene definita sulla tabella di destinazione, qualsiasi azione eseguita su tale vista deve soddisfare le condizioni per l'aggiornamento delle viste. Per altre informazioni sull'aggiornamento dei dati tramite una vista, vedere Modificare i dati tramite una vista.
• Usare la clausola WITH <common table expression> per filtrare le righe delle tabelle di origine o di destinazione. Questo metodo è simile a quello di specificare criteri di ricerca aggiuntivi nella ON clausola e potrebbe produrre risultati non corretti. Si consiglia di evitare l'utilizzo di questo metodo o di eseguirne un test accurato prima di implementarlo.

L'operazione di join nell'istruzione MERGE viene ottimizzata allo stesso modo di un join in un'istruzione SELECT . Questo significa che, durante l'elaborazione di join in SQL Server, Query Optimizer sceglie il metodo di elaborazione del join più efficiente tra quelli possibili. Quando le dimensioni dell'origine e della destinazione sono simili e le linee guida relative agli indici descritte in precedenza vengono applicate alle tabelle di origine e di destinazione, un operatore merge join è il piano di query più efficiente. Il motivo è che entrambe le tabelle vengono analizzate una sola volta e non è necessario ordinare i dati. Quando le dimensioni della tabella di origine sono inferiori rispetto a quelle della tabella di destinazione, è preferibile usare un operatore nested loops.

È possibile forzare l'uso di un join specifico specificando la OPTION (<query_hint>) clausola nell'istruzione MERGE . È consigliabile non usare l'hash join come hint per le istruzioni perché MERGE questo tipo di join non usa indici.

Procedure consigliate per la parametrizzazione

Se un'istruzione SELECT, INSERTUPDATE, o DELETE viene eseguita senza parametri, Query Optimizer di SQL Server potrebbe scegliere di parametrizzare l'istruzione internamente. ovvero di sostituire con parametri i valori letterali contenuti nella query. Ad esempio, l'istruzione INSERT dbo.MyTable (Col1, Col2) VALUES (1, 10), potrebbe essere implementata internamente come INSERT dbo.MyTable (Col1, Col2) VALUES (@p1, @p2). Questo processo, denominato parametrizzazione semplice, aumenta la capacità del motore relazionale di trovare le corrispondenze con le nuove istruzioni SQL con i piani di esecuzione compilati in precedenza esistenti. Le prestazioni delle query potrebbero essere migliorate perché la frequenza di compilazioni e ricompilazione delle query è ridotta. Query Optimizer non applica il processo di parametrizzazione semplice alle MERGE istruzioni. Pertanto, MERGE le istruzioni che contengono valori letterali potrebbero non eseguire e singole INSERTistruzioni , UPDATEo DELETE perché un nuovo piano viene compilato ogni volta che viene eseguita l'istruzione MERGE .

Per migliorare le prestazioni delle query, si consiglia di attenersi alle linee guida relative alla parametrizzazione seguenti:

• Parametrizzare tutti i valori letterali nella ON <merge_search_condition> clausola e nelle WHEN clausole dell'istruzione MERGE . Ad esempio, è possibile incorporare l'istruzione MERGE in una stored procedure sostituendo i valori letterali con i parametri di input appropriati.
• Se non è possibile parametrizzare l'istruzione, creare una guida di piano di tipo TEMPLATE e specificare l'hint per la query PARAMETERIZATION FORCED in tale guida. Per altre informazioni, vedere Specificare il comportamento di parametrizzazione delle query tramite guide di piano.
• Se MERGE le istruzioni vengono eseguite frequentemente nel database, è consigliabile impostare l'opzione PARAMETERIZATION nel database su FORCED. Si consiglia di prestare attenzione quando si imposta questa opzione, L'opzione PARAMETERIZATION è un'impostazione a livello di database e influisce sulle modalità di elaborazione delle query sul database. Per altre informazioni, vedere Parametrizzazione forzata.
• Come alternativa più recente e più semplice alle guide di piano, è consigliabile adottare una strategia simile con hint di Query Store. Per altre informazioni, vedere Hint di Query Store.

Procedure consigliate per la clausola TOP

Nell'istruzione MERGE la TOP clausola specifica il numero o la percentuale di righe interessate dopo il join della tabella di origine e della tabella di destinazione e dopo la rimozione di righe non idonee per un'azione di inserimento, aggiornamento o eliminazione. La TOP clausola riduce ulteriormente il numero di righe unite al valore specificato e le azioni di inserimento, aggiornamento o eliminazione vengono applicate alle righe unite rimanenti in modo non ordinato. Ciò significa che non esiste un ordine in cui le righe vengono distribuite tra le azioni definite nelle WHEN clausole . Ad esempio, se si specificano TOP (10) 10 righe, queste righe potrebbero essere aggiornate 7 e 3 inserite oppure 1 potrebbero essere eliminate, 5 aggiornate e 4 inserite e così via.

È comune usare la TOP clausola per eseguire operazioni DML (Data Manipulation Language) in una tabella di grandi dimensioni in batch. Quando si usa la TOP clausola nell'istruzione MERGE per questo scopo, è importante comprendere le implicazioni seguenti.

• Le prestazioni di I/O potrebbero essere interessate.

  L'istruzione MERGE esegue un'analisi completa della tabella delle tabelle di origine e di destinazione. La divisione dell'operazione in batch riduce il numero di operazioni di scrittura eseguite per batch; Tuttavia, ogni batch esegue un'analisi completa della tabella delle tabelle di origine e di destinazione. L'attività di lettura risultante potrebbe influire sulle prestazioni della query e altre attività simultanee nelle tabelle.

• Possibile restituzione di risultati non corretti.

  È importante assicurarsi che tutti i batch successivi vengano destinati a nuove righe per evitare un comportamento indesiderato, ad esempio l'inserimento non corretto di righe duplicate nella tabella di destinazione. Questa situazione può verificarsi quando nella tabella di origine è contenuta una riga non presente in un batch di destinazione, ma presente nella tabella di destinazione complessiva. Per garantire la restituzione di risultati corretti:

  • Usare la ON clausola per determinare quali righe di origine influiscono sulle righe di destinazione esistenti e quali sono effettivamente nuove.
  • Usare una condizione aggiuntiva nella WHEN MATCHED clausola per determinare se la riga di destinazione è già stata aggiornata da un batch precedente.
  • Usare una condizione aggiuntiva nella clausola e WHEN MATCHED nella SET logica per verificare che la stessa riga non possa essere aggiornata due volte.

Poiché la clausola viene applicata solo dopo l'applicazione TOP di queste clausole, ogni esecuzione inserisce una riga effettivamente non corrispondente o aggiorna una riga esistente.

Procedure consigliate per il caricamento bulk

L'istruzione MERGE può essere usata per caricare in modo efficiente i dati da un file di dati di origine in una tabella di destinazione specificando la OPENROWSET(BULK...) clausola come origine della tabella. In questo modo, l'intero file viene elaborato in un unico batch.

Per migliorare le prestazioni del processo di merge di tipo bulk, si consiglia di attenersi alle linee guida seguenti:

• Creare un indice cluster sulle colonne di join della tabella di destinazione.

• Disabilitare altri indici non univoci non cluster nella tabella di destinazione durante il caricamento MERGEbulk, abilitarli in seguito. Si tratta di una procedura comune e utile per le operazioni notturne in bulk sui dati.

• Usare gli ORDER hint e UNIQUE nella OPENROWSET(BULK...) clausola per specificare la modalità di ordinamento del file di dati di origine.

  Per impostazione predefinita, per l'operazione bulk si presume che il file di dati non sia ordinato. È quindi importante che i dati di origine vengano ordinati in base all'indice cluster nella tabella di destinazione e che l'hint ORDER venga usato per indicare l'ordine in modo che Query Optimizer possa generare un piano di query più efficiente. Gli hint vengono convalidati in fase di esecuzione. Se il flusso di dati non è conforme agli hint specificati, viene generato un errore.

Queste linee guida garantiscono che le chiavi di join siano univoche e che l'ordinamento dei dati nel file di origine corrisponda alla tabella di destinazione. Le prestazioni delle query risultano migliorate perché non sono necessarie ulteriori operazioni di ordinamento né vengono richieste copie dei dati non necessarie.

Misurare e diagnosticare le prestazioni MERGE

Le funzionalità seguenti sono disponibili per facilitare la misurazione e la diagnosi delle prestazioni delle MERGE istruzioni.

• Usare il contatore stmt di merge nella vista a gestione dinamica sys.dm_exec_query_optimizer_info per restituire il numero di ottimizzazioni delle query per MERGE le istruzioni .
• Usare l'attributo merge_action_type nella sys.dm_exec_plan_attributes visualizzazione a gestione dinamica per restituire il tipo di piano di esecuzione del trigger usato come risultato di un'istruzione MERGE .
• Usare una sessione eventi estesi per raccogliere i dati di risoluzione dei problemi per l'istruzione MERGE nello stesso modo in cui si farebbe per altre istruzioni DML (Data Manipulation Language). Per altre informazioni su Eventi estesi, vedere Avvio rapido: Eventi estesi e Utilizzare Profiler XEvent di SSMS.

Examples

A. Usare MERGE per eseguire operazioni INSERT e UPDATE in una tabella in un'unica istruzione

Uno scenario comune è l'aggiornamento di una o più colonne in una tabella se esiste una riga corrispondente oppure l'inserimento dei dati come nuova riga se non esiste una riga corrispondente. In genere si esegue uno scenario passando parametri a una stored procedure che contiene le istruzioni e UPDATE appropriateINSERT. Con l'istruzione è possibile eseguire entrambe le MERGE attività in un'unica istruzione. Il seguente esempio mostra una procedura memorizzata nel database AdventureWorks2025 che contiene sia un'istruzione INSERT che un'istruzione UPDATE . La routine viene quindi modificata per eseguire le operazioni equivalenti usando una singola MERGE istruzione.

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. Usare MERGE per eseguire operazioni UPDATE e DELETE in una tabella in una singola istruzione

Il seguente esempio serve MERGE ad aggiornare la ProductInventory tabella nel database di esempio AdventureWorks2025, quotidianamente, in base agli ordini elaborati nella SalesOrderDetail tabella. La colonna Quantity della tabella ProductInventory viene aggiornata sottraendo il numero di ordini effettuati ogni giorno per ciascun prodotto nella tabella SalesOrderDetail. Se il numero di ordini per un prodotto riduce il livello delle scorte del prodotto a zero o a un valore inferiore, la riga relativa a tale prodotto viene eliminata dalla tabella ProductInventory.

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. Usare MERGE per eseguire operazioni UPDATE e INSERT in una tabella di destinazione usando una tabella di origine derivata

Il seguente esempio viene utilizzato MERGE per modificare la SalesReason tabella nel database AdventureWorks2025 aggiornando o inserendo righe.

Quando il valore di NewName nella tabella di origine corrisponde a un valore della colonna Name nella tabella di destinazione (SalesReason), la colonna ReasonType viene aggiornata nella tabella di destinazione. Quando il valore di NewName non corrisponde, la riga di origine viene inserita nella tabella di destinazione. La tabella di origine è una tabella derivata che utilizza il costruttore di valori di tabella di Transact-SQL per specificare più righe per la tabella di origine. Per altre informazioni sull'uso del costruttore di valori di tabella in una tabella derivata, vedere Costruttore di valori di tabella (Transact-SQL).

La OUTPUT clausola può essere utile per eseguire una query sul risultato delle MERGE istruzioni. Per altre informazioni, vedere clausola OUTPUT (Transact-SQL). Nell'esempio viene inoltre illustrato come archiviare i risultati della OUTPUT clausola in una variabile di tabella. Si riepiloga quindi i risultati dell'istruzione MERGE eseguendo una semplice operazione di selezione che restituisce il conteggio delle righe inserite e aggiornate.

-- 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. Inserire i risultati dell'istruzione MERGE in un'altra tabella

Nell'esempio seguente vengono acquisiti i dati restituiti dalla OUTPUT clausola di un'istruzione MERGE e tali dati vengono inseriti in un'altra tabella. L'estratto MERGE aggiorna la Quantity colonna della ProductInventory tabella nel database AdventureWorks2025, basandosi sugli ordini elaborati nella SalesOrderDetail tabella. L'esempio acquisisce le righe aggiornate e le inserisce in un'altra tabella usata per tenere traccia delle modifiche apportate alle scorte.

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. Usare MERGE per eseguire INSERT o UPDATE in una tabella perimetrale di destinazione in un database a grafo

In questo esempio si creano le tabelle nodi Person e City e la tabella bordi livesIn. Usare l'istruzione MERGE sul livesIn bordo e inserire una nuova riga se il bordo non esiste già tra un Person oggetto e City. Se il bordo esiste già, si aggiorna solo l'attributo StreetAddress sul bordo livesIn.

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

Contenuti correlati

• SELECT (Transact-SQL)
• INSERIMENTO (Transact-SQL)
• AGGIORNAMENTO (Transact-SQL)
• CANCELLA (Transact-SQL)
• Clausola OUTPUT (Transact-SQL)
• MERGE nei pacchetti di Integration Services
• Clausola FROM più JOIN, APPLY, PIVOT (Transact-SQL)
• Costruttore Table Value (Transact-SQL)