Udostępnij za pomocą

MERGE (Transact-SQL)

Dotyczy do:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics (dedykowana pula SQL only)Baza danych SQL w Microsoft FabricMagazyn w Microsoft Fabric

Instrukcja MERGE uruchamia operacje wstawiania, aktualizowania lub usuwania w tabeli docelowej z wyników sprzężenia z tabelą źródłową. Na przykład zsynchronizuj dwie tabele, wstawiając, aktualizując lub usuwając wiersze w jednej tabeli na podstawie różnic znalezionych w drugiej tabeli.

Ten artykuł zawiera różne składnie, argumenty, uwagi, uprawnienia i przykłady na podstawie wybranej wersji produktu. Wybierz odpowiednią wersję produktu z listy rozwijanej Wersja.

Note

W usłudze Fabric Data Warehouse MERGE jest dostępna wersja zapoznawcza.

Transact-SQL konwencje składni

Syntax

Składnia dla programu SQL Server i usługi Azure SQL Database:

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

Składnia dla usługi Azure Synapse Analytics, magazynu danych sieci szkieletowej:

[ 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

Z <common_table_expression>

Określa tymczasowy nazwany zestaw wyników lub widok, znany również jako wspólne wyrażenie tabeli, które jest zdefiniowane w zakresie instrukcji MERGE . Zestaw wyników pochodzi z prostego zapytania i jest przywołyny przez instrukcję MERGE . Aby uzyskać więcej informacji, zobacz WITH common_table_expression (Transact-SQL).

TOP ( wyrażenie ) [ PERCENT ]

Określa liczbę lub procent wierszy, których dotyczy problem. wyrażenie może być liczbą lub procentem wierszy. Wiersze, do których odwołuje się TOP wyrażenie, nie są rozmieszczane w żadnej kolejności. Aby uzyskać więcej informacji, zobacz TOP (Transact-SQL).

Klauzula TOP ma zastosowanie po usunięciu całej tabeli źródłowej i całego sprzężenia tabeli docelowej oraz sprzężonych wierszy, które nie kwalifikują się do akcji wstawiania, aktualizowania ani usuwania. Klauzula TOP dodatkowo zmniejsza liczbę sprzężonych wierszy do określonej wartości. Te akcje (wstawianie, aktualizowanie lub usuwanie) mają zastosowanie do pozostałych sprzężonych wierszy w sposób nieurządkowany. Oznacza to, że nie ma kolejności, w której wiersze są dystrybuowane między akcje zdefiniowane w WHEN klauzulach. Na przykład określenie TOP (10) wpływa na 10 wierszy. Z tych wierszy można zaktualizować 7 i wstawić 3 lub 1 można usunąć, zaktualizować 5 i 4 wstawić itd.

Bez filtrów w tabeli MERGE źródłowej instrukcja może wykonać skanowanie tabeli lub skanowanie indeksu klastrowanego w tabeli źródłowej, a także skanowanie tabeli lub skanowanie indeksu klastrowanego tabeli docelowej. W związku z tym wydajność operacji we/wy czasami ma wpływ nawet w przypadku używania TOP klauzuli w celu zmodyfikowania dużej tabeli przez utworzenie wielu partii. W tym scenariuszu należy upewnić się, że wszystkie kolejne partie są przeznaczone dla nowych wierszy.

database_name

Nazwa bazy danych, w której znajduje się target_table .

schema_name

Nazwa schematu, do którego należy target_table .

target_table

Tabela lub widok, względem którego są dopasowywane wiersze <table_source> danych na <clause_search_condition>podstawie elementu . target_table jest obiektem docelowym wszystkich operacji wstawiania, aktualizowania lub usuwania określonych przez WHEN klauzule instrukcji MERGE .

Jeśli target_table jest widokiem, wszelkie akcje względem niego muszą spełniać warunki aktualizacji widoków. Aby uzyskać więcej informacji, zobacz Modyfikowanie danych za pośrednictwem widoku.

target_table nie może być tabelą zdalną. target_table nie może mieć zdefiniowanych żadnych reguł. target_table nie może być tabelą zoptymalizowaną pod kątem pamięci.

Wskazówki można określić jako <merge_hint>.

<merge_hint> nie jest obsługiwana w usłudze Azure Synapse Analytics.

[ JAKO ] table_alias

Alternatywna nazwa odwołująca się do tabeli dla target_table.

UŻYWANIE <table_source>

Określa źródło danych, które jest zgodne z wierszami danych w target_table na <merge_search_condition>podstawie . Wynik tego dopasowania określa akcje, które mają być podejmowane przez WHEN klauzule instrukcji MERGE . <table_source> może być tabelą zdalną lub tabelą pochodną, która uzyskuje dostęp do tabel zdalnych.

<table_source> może być tabelą pochodną, która używa konstruktora wartości tabeli Transact-SQL do konstruowania tabeli, określając wiele wierszy.

<table_source> może być tabelą pochodną, która służy SELECT ... UNION ALL do konstruowania tabeli, określając wiele wierszy.

[ JAKO ] table_alias

Alternatywna nazwa odwołująca się do tabeli dla table_source.

Aby uzyskać więcej informacji na temat składni i argumentów tej klauzuli, zobacz FROM (Transact-SQL).

W <merge_search_condition>

Określa warunki, w których <table_source> sprzężenia z target_table określić, gdzie są zgodne.

Caution

Ważne jest, aby określić tylko kolumny z tabeli docelowej do użycia w celu dopasowania. Oznacza to, że określ kolumny z tabeli docelowej, które są porównywane z odpowiednią kolumną tabeli źródłowej. Nie próbuj poprawiać wydajności zapytań, filtrując wiersze w tabeli docelowej w ON klauzuli , na przykład, na przykład określając AND NOT target_table.column_x = valuewartość . Może to zwrócić nieoczekiwane i nieprawidłowe wyniki.

PO DOPASOWANIU MERGE_MATCHED <>

Określa, że wszystkie wiersze *target_table, które pasują do wierszy zwracanych przez <table_source> ON <merge_search_condition>, i spełniają wszelkie dodatkowe warunki wyszukiwania, są aktualizowane lub usuwane zgodnie z klauzulą <merge_matched> .

Instrukcja MERGE może mieć co najwyżej dwie WHEN MATCHED klauzule. Jeśli określono dwie klauzule, pierwsza klauzula musi towarzyszyć klauzuli AND<search_condition> . W przypadku dowolnego wiersza druga WHEN MATCHED klauzula jest stosowana tylko wtedy, gdy pierwsza nie jest. Jeśli istnieją dwie WHEN MATCHED klauzule, należy określić UPDATE akcję i należy określić DELETE akcję. Gdy UPDATE jest określony w klauzuli <merge_matched> , a więcej niż jeden wiersz <table_source> pasuje do wiersza w target_table na <merge_search_condition>podstawie , program SQL Server zwraca błąd. Instrukcja MERGE nie może zaktualizować tego samego wiersza więcej niż raz ani zaktualizować i usunąć ten sam wiersz.

W PRZYPADKU BRAKU DOPASOWANIA [WEDŁUG WARTOŚCI DOCELOWEJ] MERGE_NOT_MATCHED <>

Określa, że wiersz jest wstawiany do target_table dla każdego wiersza zwróconego przez <table_source> ON <merge_search_condition> ten wiersz nie pasuje do wiersza w target_table, ale spełnia dodatkowy warunek wyszukiwania, jeśli istnieje. Wartości do wstawienia są określane przez klauzulę <merge_not_matched> . Instrukcja MERGE może mieć tylko jedną WHEN NOT MATCHED [ BY TARGET ] klauzulę.

GDY ŹRÓDŁO NIE JEST ZGODNE <, MERGE_MATCHED>

Określa, że wszystkie wiersze *target_table, które nie pasują do wierszy zwracanych przez <table_source> ON <merge_search_condition>, i które spełniają wszelkie dodatkowe warunki wyszukiwania, są aktualizowane lub usuwane zgodnie z klauzulą <merge_matched> .

Instrukcja MERGE może mieć co najwyżej dwie WHEN NOT MATCHED BY SOURCE klauzule. Jeśli określono dwie klauzule, pierwsza klauzula musi towarzyszyć klauzuli AND<clause_search_condition> . W przypadku dowolnego wiersza druga WHEN NOT MATCHED BY SOURCE klauzula jest stosowana tylko wtedy, gdy pierwsza nie jest. Jeśli istnieją dwie WHEN NOT MATCHED BY SOURCE klauzule, należy określić UPDATE akcję i określić DELETE akcję. W tabeli docelowej można odwoływać się tylko do kolumn.<clause_search_condition>

Gdy nie można uzyskać dostępu do kolumn w tabeli źródłowej, nie można uzyskać dostępu do żadnych wierszy w <table_source>tabeli źródłowej. Jeśli akcja aktualizacji lub usunięcia określona w <merge_matched> klauzuli odwołuje się do kolumn w tabeli źródłowej, zwracany jest błąd 207 (nieprawidłowa nazwa kolumny). Na przykład klauzula WHEN NOT MATCHED BY SOURCE THEN UPDATE SET TargetTable.Col1 = SourceTable.Col1 może spowodować niepowodzenie instrukcji, ponieważ Col1 w tabeli źródłowej jest niedostępna.

I clause_search_condition <>

Określa dowolny prawidłowy warunek wyszukiwania. Aby uzyskać więcej informacji, zobacz Warunek wyszukiwania (Transact-SQL).

<table_hint_limited>

Określa co najmniej jedną wskazówkę tabeli, która ma być stosowana w tabeli docelowej dla każdej akcji wstawiania, aktualizowania lub usuwania wykonywanej przez instrukcję MERGE . Słowo WITH kluczowe i nawiasy są wymagane.

NOLOCK i READUNCOMMITTED nie są dozwolone. Aby uzyskać więcej informacji na temat wskazówek dotyczących tabel, zobacz Wskazówki dotyczące tabel (Transact-SQL).

Określenie TABLOCK wskazówki dotyczącej tabeli, która jest celem instrukcji INSERT , ma taki sam wpływ, jak określenie TABLOCKX wskazówki. Na stole jest pobierana blokada na wyłączność. Po określeniu elementu FORCESEEK ma zastosowanie do niejawnego wystąpienia tabeli docelowej połączonej z tabelą źródłową.

Caution

Określenie za pomocą READPASTWHEN NOT MATCHED [ BY TARGET ] THEN INSERT polecenia może spowodować INSERT operacje naruszające UNIQUE ograniczenia.

INDEX ( index_val [ ,... n ] )

Określa nazwę lub identyfikator co najmniej jednego indeksu w tabeli docelowej do wykonywania niejawnego sprzężenia z tabelą źródłową. Aby uzyskać więcej informacji, zobacz Wskazówki dotyczące tabel (Transact-SQL).

<output_clause>

Zwraca wiersz dla każdego wiersza w target_table , który jest aktualizowany, wstawiany lub usuwany, w żadnej określonej kolejności. $action można określić w klauzuli output. $actionjest kolumną typu nvarchar(10), która zwraca jedną z trzech wartości dla każdego wiersza: INSERT, lub UPDATEDELETE, zgodnie z akcją wykonaną w tym wierszu. Klauzula OUTPUT jest zalecanym sposobem wykonywania zapytań lub zliczania wierszy, których dotyczy element MERGE. Aby uzyskać więcej informacji na temat argumentów i zachowania tej klauzuli, zobacz klauzula OUTPUT (Transact-SQL).

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

Określa, że wskazówki optymalizatora są używane do dostosowywania sposobu przetwarzania instrukcji przez aparat bazy danych. Aby uzyskać więcej informacji, zobacz Wskazówki dotyczące zapytań (Transact-SQL).

<merge_matched>

Określa akcję aktualizacji lub usuwania, która jest stosowana do wszystkich wierszy target_table , które nie pasują do wierszy zwracanych przez <table_source> ON <merge_search_condition>element , i które spełniają wszelkie dodatkowe warunki wyszukiwania.

SET_CLAUSE ZESTAWU <AKTUALIZACJI>

Określa listę nazw kolumn lub zmiennych do zaktualizowania w tabeli docelowej oraz wartości, za pomocą których mają być aktualizowane.

Aby uzyskać więcej informacji na temat argumentów tej klauzuli, zobacz UPDATE (Transact-SQL). Ustawienie zmiennej na tę samą wartość co kolumna nie jest obsługiwane.

DELETE

Określa, że wiersze pasujące do wierszy w target_table są usuwane.

<merge_not_matched>

Określa wartości do wstawienia do tabeli docelowej.

( column_list )

Lista co najmniej jednej kolumny tabeli docelowej, w której mają być wstawiane dane. Kolumny muszą być określone jako nazwa pojedynczej części lub w przeciwnym razie instrukcja kończy się niepowodzeniem MERGE . column_list muszą być ujęte w nawiasy i rozdzielane przecinkami.

WARTOŚCI ( values_list )

Rozdzielona przecinkami lista stałych, zmiennych lub wyrażeń, które zwracają wartości do wstawienia do tabeli docelowej. Wyrażenia nie mogą zawierać instrukcji EXECUTE .

WARTOŚCI DOMYŚLNE

Wymusza wstawiony wiersz, aby zawierał wartości domyślne zdefiniowane dla każdej kolumny.

Aby uzyskać więcej informacji na temat tej klauzuli, zobacz INSERT (Transact-SQL).

<search_condition>

Określa warunki wyszukiwania do określenia <merge_search_condition> lub <clause_search_condition>. Aby uzyskać więcej informacji na temat argumentów dla tej klauzuli, zobacz Warunek wyszukiwania (Transact-SQL).

<wzorzec wyszukiwania grafu>

Określa wzorzec dopasowania grafu. Aby uzyskać więcej informacji na temat argumentów dla tej klauzuli, zobacz MATCH (Transact-SQL).

Remarks

Zachowanie warunkowe opisane dla instrukcji MERGE działa najlepiej, gdy obie tabele mają złożoną mieszankę pasujących cech. Na przykład wstawienie wiersza, jeśli nie istnieje, lub zaktualizowanie wiersza, jeśli jest on zgodny. Po prostu aktualizując jedną tabelę na podstawie wierszy innej tabeli, zwiększ wydajność i skalowalność za pomocą INSERTinstrukcji , UPDATEi DELETE . Przykład:

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

Należy określić co najmniej jedną z trzech MATCHED klauzul, ale można je określić w dowolnej kolejności. Nie można zaktualizować zmiennej więcej niż raz w tej samej MATCHED klauzuli.

Każda akcja wstawiania, aktualizowania lub usuwania określona w tabeli docelowej przez MERGE instrukcję jest ograniczona przez wszelkie zdefiniowane ograniczenia, w tym wszelkie ograniczenia więzów integralności odwołań. Jeśli IGNORE_DUP_KEY dotyczy ON żadnych unikatowych indeksów w tabeli docelowej, MERGE ignoruje to ustawienie.

Instrukcja MERGE wymaga średnika (;) jako terminatora instrukcji. Błąd 10713 jest zgłaszany, gdy MERGE instrukcja jest uruchamiana bez terminatora.

W przypadku użycia po MERGE@@ROWCOUNT (Transact-SQL) zwraca całkowitą liczbę wstawionych, zaktualizowanych i usuniętych wierszy do klienta.

MERGE jest w pełni zastrzeżonym słowem kluczowym, gdy poziom zgodności bazy danych jest ustawiony na 100 lub wyższy. Instrukcja MERGE jest dostępna zarówno 90 na poziomach zgodności bazy danych, jak i 100 na poziomie zgodności bazy danych, jednak słowo kluczowe nie jest w pełni zarezerwowane, gdy poziom zgodności bazy danych jest ustawiony na 90.

Caution

Nie używaj instrukcji MERGE podczas korzystania z replikacji aktualizacji w kolejce. MERGE Wyzwalacz aktualizacji w kolejce i nie jest zgodny. Zastąp instrukcję MERGE instrukcją i INSERTUPDATE .

Zagadnienia dotyczące usługi Azure Synapse Analytics

W usłudze Azure Synapse Analytics MERGE polecenie ma następujące różnice w porównaniu z programem SQL Server i usługą Azure SQL Database.

  • Użycie MERGE polecenia w celu zaktualizowania kolumny klucza dystrybucji nie jest obsługiwane w kompilacjach starszych niż 10.0.17829.0. Jeśli nie można wstrzymać lub wymusić uaktualnienia, użyj instrukcji ANSI UPDATE FROM ... JOIN jako obejścia do wersji 10.0.17829.0.
  • MERGE Aktualizacja jest implementowana jako para usuwania i wstawiania. Liczba wierszy, których MERGE dotyczy aktualizacja, obejmuje usunięte i wstawione wiersze.
  • MERGE...WHEN NOT MATCHED INSERT nie jest obsługiwana w przypadku tabel z kolumnami IDENTITY .
  • Konstruktor wartości tabeli nie może być używany w USING klauzuli dla tabeli źródłowej. Użyj SELECT ... UNION ALL polecenia , aby utworzyć pochodną tabelę źródłową z wieloma wierszami.
  • Obsługa tabel z różnymi typami dystrybucji jest opisana w tej tabeli:
KLAUZULA MERGE w usłudze Azure Synapse Analytics Obsługiwana TARGET tabela dystrybucji Obsługiwana tabela dystrybucji SOURCE Comment
WHEN MATCHED Wszystkie typy dystrybucji Wszystkie typy dystrybucji
NOT MATCHED BY TARGET HASH Wszystkie typy dystrybucji Użyj polecenia UPDATE/DELETE FROM...JOIN , aby zsynchronizować dwie tabele.
NOT MATCHED BY SOURCE Wszystkie typy dystrybucji Wszystkie typy dystrybucji

Tip

Jeśli używasz klucza skrótu dystrybucji jako JOIN kolumny w MERGE pliku i wykonujesz tylko porównanie równości, możesz pominąć klucz dystrybucji z listy kolumn w WHEN MATCHED THEN UPDATE SET klauzuli, ponieważ jest to nadmiarowa aktualizacja.

W usłudze Azure Synapse Analytics MERGE polecenie w kompilacjach starszych niż 10.0.17829.0 może w pewnych warunkach pozostawić tabelę docelową w niespójnym stanie, a wiersze umieszczone w niewłaściwej dystrybucji powodują, że późniejsze zapytania zwracają nieprawidłowe wyniki w niektórych przypadkach. Ten problem może wystąpić w 2 przypadkach:

Scenario Comment
Przypadek 1
Użycie MERGE w tabeli rozproszonej TARGET HASH zawierającej indeksy pomocnicze lub UNIQUE ograniczenie.		 — Naprawiono w programie Synapse SQL 10.0.15563.0 i nowszych wersjach.
— Jeśli SELECT @@VERSION zwraca niższą wersję niż 10.0.15563.0, ręcznie wstrzymaj pulę SQL usługi Synapse i wznów ją, aby pobrać tę poprawkę.
— Dopóki poprawka nie zostanie zastosowana do puli SQL usługi Synapse, unikaj używania MERGE polecenia w HASH tabelach rozproszonych TARGET , które mają indeksy pomocnicze lub UNIQUE ograniczenia.
Przypadek 2
Używanie funkcji MERGE do aktualizowania kolumny klucza dystrybucji tabeli rozproszonej hash.		 — Naprawiono w programie Synapse SQL 10.0.17829.0 i nowszych wersjach.
— Jeśli SELECT @@VERSION zwraca niższą wersję niż 10.0.17829.0, ręcznie wstrzymaj i wznów pulę sql usługi Synapse, aby pobrać tę poprawkę.
— Dopóki poprawka nie zostanie zastosowana do puli SQL usługi Synapse, unikaj używania MERGE polecenia w celu zaktualizowania kolumn kluczy dystrybucji.

Aktualizacje w obu scenariuszach nie naprawiają tabel, których dotyczy już poprzednie MERGE wykonanie. Użyj poniższych skryptów, aby ręcznie zidentyfikować i naprawić wszystkie tabele, których dotyczy problem.

Aby sprawdzić, które HASH tabele rozproszone w bazie danych mogą być niepokojące (jeśli są używane w poprzednich przypadkach), uruchom następującą instrukcję:

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

Aby sprawdzić, czy na tabelę HASH rozproszoną MERGE ma wpływ przypadek 1 lub przypadek 2, wykonaj następujące kroki, aby sprawdzić, czy tabele mają wiersze w niewłaściwej dystrybucji. Jeśli no need for repair zostanie zwrócona, ta tabela nie ma wpływu.

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

Aby naprawić tabele, których dotyczy problem, uruchom te instrukcje, aby skopiować wszystkie wiersze ze starej tabeli do nowej tabeli.

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

Troubleshooting

W niektórych scenariuszach MERGE instrukcja może spowodować błąd CREATE TABLE failed because column <> in table <> exceeds the maximum of 1024 columns., nawet jeśli tabela docelowa lub źródłowa nie ma 1024 kolumn. Ten scenariusz może wystąpić, gdy zostaną spełnione dowolne z następujących warunków:

  • W obiekcie , lub operacji określono DELETEwiele kolumn (nie dotyczy żadnej UPDATE SET klauzuli)INSERTMERGEWHEN [NOT] MATCHED
  • Dowolna kolumna JOIN w warunku ma indeks nieklastrowany (NCI)
  • Tabela docelowa jest HASH rozproszona

Jeśli ten błąd zostanie znaleziony, sugerowane obejścia są następujące:

  • Usuń indeks nieklastrowany (NCI) z JOIN kolumn lub sprzężenia w kolumnach bez identyfikatora NCI. Jeśli później zaktualizujesz tabele bazowe w celu uwzględnienia NCI w JOIN kolumnach, instrukcja MERGE może być podatna na ten błąd w czasie wykonywania. Aby uzyskać więcej informacji, zobacz DROP INDEX (DROP INDEX).
  • Użyj instrukcji UPDATE, DELETE i INSERT zamiast MERGE.

Implementacja wyzwalacza

Dla każdej akcji wstawiania, aktualizowania lub usuwania określonej w MERGE instrukcji program SQL Server uruchamia wszystkie odpowiednie AFTER wyzwalacze zdefiniowane w tabeli docelowej, ale nie gwarantuje, która akcja wyzwalaczy jest uruchamiana jako pierwsza lub ostatnia. Wyzwalacze zdefiniowane dla tej samej akcji uznają określoną kolejność. Aby uzyskać więcej informacji na temat ustawiania kolejności wyzwalania wyzwalacza, zobacz Określanie pierwszych i ostatnich wyzwalaczy.

Jeśli tabela docelowa ma włączony INSTEAD wyzwalacz OF zdefiniowany dla akcji wstawiania, aktualizowania lub usuwania wykonywanej przez MERGE instrukcję, musi mieć włączony INSTEAD wyzwalacz OF dla wszystkich akcji określonych w instrukcji MERGE .

Jeśli którykolwiek INSTEAD z wyzwalaczy OF UPDATE lub INSTEAD OF DELETE jest zdefiniowany na target_table, operacje aktualizacji lub usuwania nie są uruchamiane. Zamiast tego wyzwalacze są wyzwalane, a następnie wstawione i usunięte tabele są wypełniane odpowiednio.

Jeśli w INSTEAD zdefiniowano jakiekolwiek INSERT wyzwalacze OF, operacja wstawiania nie jest wykonywana. Zamiast tego tabela zostanie wypełniona odpowiednio.

Note

W przeciwieństwie do oddzielnych INSERTinstrukcji , UPDATEi DELETE liczba wierszy odzwierciedlonych wewnątrz @@ROWCOUNT wyzwalacza może być wyższa. Wewnątrz @@ROWCOUNT dowolnego AFTER wyzwalacza (niezależnie od instrukcji modyfikacji danych wyzwalacz przechwytuje) będzie odzwierciedlać całkowitą liczbę wierszy, których dotyczy MERGE. Jeśli na przykład instrukcja MERGE wstawia jeden wiersz, aktualizuje jeden wiersz i usuwa jeden wiersz, @@ROWCOUNT będzie trzy dla dowolnego AFTER wyzwalacza, nawet jeśli wyzwalacz jest zadeklarowany tylko dla INSERT instrukcji.

Permissions

Wymaga SELECT uprawnień do tabeli źródłowej i INSERT, UPDATElub DELETE uprawnień w tabeli docelowej. Aby uzyskać więcej informacji, zobacz sekcję Uprawnienia w artykułach SELECT (Transact-SQL), INSERT (Transact-SQL), UPDATE (Transact-SQL) i DELETE (Transact-SQL).

Najlepsze rozwiązania dotyczące indeksowania

Za pomocą instrukcji MERGE można zastąpić poszczególne instrukcje DML pojedynczą instrukcją. Może to poprawić wydajność zapytań, ponieważ operacje są wykonywane w ramach pojedynczej instrukcji, dlatego minimalizując liczbę przetwarzanych danych w tabelach źródłowych i docelowych. Jednak wzrost wydajności zależy od prawidłowych indeksów, sprzężeń i innych zagadnień.

Aby poprawić wydajność instrukcji MERGE , zalecamy następujące wytyczne dotyczące indeksu:

  • Utwórz indeksy, aby ułatwić sprzężenie między źródłem i elementem docelowym elementu MERGE:
    • Utwórz indeks w kolumnach sprzężenia w tabeli źródłowej zawierającej klucze obejmujące logikę sprzężenia do tabeli docelowej. Jeśli to możliwe, powinien być unikatowy.
    • Ponadto utwórz indeks w kolumnach sprzężenia w tabeli docelowej. Jeśli to możliwe, powinien być unikatowym indeksem klastrowanym.
    • Te dwa indeksy zapewniają, że dane w tabelach są sortowane, a unikatowość ułatwia wydajność porównania. Wydajność zapytań jest ulepszona, ponieważ optymalizator zapytań nie musi wykonywać dodatkowego przetwarzania weryfikacji w celu zlokalizowania i zaktualizowania zduplikowanych wierszy oraz dodatkowych operacji sortowania nie jest konieczne.
  • Unikaj tabel z dowolną formą indeksu magazynu kolumn jako element docelowy instrukcji MERGE . Podobnie jak w przypadku wszystkich upDATEs, wydajność może być lepsza w indeksach magazynu kolumn, aktualizując tabelę magazynu wierszy etapowych, a następnie wykonując przetwarzanie wsadowe DELETE i INSERT, zamiast UPDATE lub MERGE.

Zagadnienia dotyczące współbieżności dla scalania

Jeśli chodzi o blokowanie, MERGE różni się od dyskretnych, kolejnych INSERTinstrukcji , UPDATEi DELETE . MERGE nadal wykonuje operacje INSERT, UPDATEi DELETE , jednak przy użyciu różnych mechanizmów blokowania. Bardziej wydajne może być pisanie dyskretnych INSERTinstrukcji , UPDATEi DELETE dla niektórych potrzeb aplikacji. Na dużą skalę MERGE może wprowadzać skomplikowane problemy ze współbieżnością lub wymagać zaawansowanego rozwiązywania problemów. W związku z tym należy zaplanować dokładne przetestowanie dowolnej MERGE instrukcji przed wdrożeniem w środowisku produkcyjnym.

MERGE instrukcje są odpowiednim zamiennikiem dyskretnych INSERToperacji , UPDATEi DELETE w (ale nie tylko) następujących scenariuszy:

  • Operacje ETL obejmujące duże liczby wierszy są wykonywane w czasie, gdy inne operacje współbieżne nie są oczekiwane*. Jeśli oczekuje się dużej współbieżności, oddzielne elementy INSERT, UPDATEi DELETE logika mogą działać lepiej, z mniejszym blokowaniem MERGE niż instrukcja.
  • Złożone operacje obejmujące małe liczby wierszy i transakcje prawdopodobnie nie będą wykonywane przez dłuższy czas.
  • Złożone operacje obejmujące tabele użytkowników, w których indeksy można zaprojektować, aby zapewnić optymalne plany wykonywania, unikając skanowania tabel i wyszukiwań na rzecz skanowania indeksów lub — najlepiej — wyszukiwania indeksów.

Inne zagadnienia dotyczące współbieżności:

  • W niektórych scenariuszach, w których oczekuje się, że unikatowe klucze zostaną wstawione i zaktualizowane przez MERGEparametr , określenie parametru HOLDLOCK zapobiegnie naruszeniom unikatowych kluczy. HOLDLOCK jest synonimem SERIALIZABLE poziomu izolacji transakcji, który nie zezwala na inne współbieżne transakcje w celu modyfikowania danych odczytanych przez tę transakcję. SERIALIZABLE jest najbezpieczniejszym poziomem izolacji, ale zapewnia najmniejszą współbieżność z innymi transakcjami, które zachowują blokady na zakresach danych, aby zapobiec wstawieniu lub zaktualizowaniu wierszy phantom podczas wykonywania operacji odczytu. Aby uzyskać więcej informacji na temat HOLDLOCKprogramu , zobacz Wskazówki dotyczące tabel i USTAW POZIOM IZOLACJI TRANSAKCJI (Transact-SQL).

Najlepsze rozwiązania dotyczące dołączania

Aby poprawić wydajność instrukcji i zapewnić uzyskanie prawidłowych MERGE wyników, zalecamy następujące wskazówki dotyczące dołączania:

  • Określ tylko warunki wyszukiwania w klauzuli ON <merge_search_condition> , która określa kryteria dopasowywania danych w tabelach źródłowych i docelowych. Oznacza to, że określ tylko kolumny z tabeli docelowej, które są porównywane z odpowiednimi kolumnami tabeli źródłowej.
  • Nie dołączaj porównań do innych wartości, takich jak stała.

Aby odfiltrować wiersze z tabel źródłowych lub docelowych, użyj jednej z następujących metod.

  • Określ warunek wyszukiwania filtrowania wierszy w odpowiedniej WHEN klauzuli. Na przykład WHEN NOT MATCHED AND S.EmployeeName LIKE 'S%' THEN INSERT....
  • Zdefiniuj widok w źródle lub obiekcie docelowym, który zwraca przefiltrowane wiersze i odwołuje się do widoku jako tabeli źródłowej lub docelowej. Jeśli widok jest zdefiniowany w tabeli docelowej, wszelkie akcje względem niej muszą spełniać warunki aktualizacji widoków. Aby uzyskać więcej informacji na temat aktualizowania danych przy użyciu widoku, zobacz Modyfikowanie danych za pośrednictwem widoku.
  • Użyj klauzuli WITH <common table expression> , aby odfiltrować wiersze z tabel źródłowych lub docelowych. Ta metoda jest podobna do określania dodatkowych kryteriów wyszukiwania w klauzuli ON i może generować nieprawidłowe wyniki. Zalecamy unikanie używania tej metody lub dokładnego testowania przed jej wdrożeniem.

Operacja sprzężenia w instrukcji MERGE jest zoptymalizowana w taki sam sposób jak sprzężenia w instrukcji SELECT . Oznacza to, że gdy program SQL Server przetwarza sprzężenia, optymalizator zapytań wybiera najbardziej wydajną metodę (z kilku możliwości) przetwarzania sprzężenia. Jeśli źródło i element docelowy mają podobny rozmiar, a opisane wcześniej wytyczne dotyczące indeksu są stosowane do tabel źródłowych i docelowych, operator sprzężenia scalania jest najbardziej wydajnym planem zapytania. Dzieje się tak, ponieważ obie tabele są skanowane raz i nie trzeba sortować danych. Jeśli źródło jest mniejsze niż tabela docelowa, preferowany jest operator zagnieżdżonych pętli.

Użycie określonego sprzężenia można wymusić, określając klauzulę OPTION (<query_hint>) w instrukcji MERGE . Zalecamy, aby nie używać sprzężenia skrótu jako wskazówki zapytania dla MERGE instrukcji, ponieważ ten typ sprzężenia nie używa indeksów.

Najlepsze rozwiązania dotyczące parametryzacji

SELECTJeśli instrukcja , INSERT, UPDATElub DELETE jest wykonywana bez parametrów, optymalizator zapytań programu SQL Server może zdecydować się na sparametryzowanie instrukcji wewnętrznie. Oznacza to, że wszystkie wartości literału zawarte w zapytaniu są zastępowane parametrami. Na przykład instrukcja INSERT dbo.MyTable (Col1, Col2) VALUES (1, 10), może zostać zaimplementowana wewnętrznie jako INSERT dbo.MyTable (Col1, Col2) VALUES (@p1, @p2). Ten proces, nazywany prostą parametryzacji, zwiększa zdolność aparatu relacyjnego do dopasowywania nowych instrukcji SQL z istniejącymi wcześniej skompilowanymi planami wykonywania. Wydajność zapytań może zostać ulepszona, ponieważ częstotliwość kompilacji zapytań i ponowne kompilowanie jest ograniczona. Optymalizator zapytań nie stosuje prostego procesu parametryzacji do MERGE instrukcji. MERGE W związku z tym instrukcje zawierające wartości literału mogą nie wykonywać poszczególnych INSERTinstrukcji , lub , UPDATEponieważ DELETE nowy plan jest kompilowany za każdym razem, MERGE gdy instrukcja jest wykonywana.

Aby zwiększyć wydajność zapytań, zalecamy następujące wytyczne dotyczące parametryzacji:

  • Parametryzacja wszystkich wartości literałów w klauzuli ON <merge_search_condition> i w WHEN klauzulach instrukcji MERGE . Można na przykład dołączyć instrukcję MERGE do procedury składowanej, zastępując wartości literału odpowiednimi parametrami wejściowymi.
  • Jeśli nie możesz sparametryzować instrukcji, utwórz przewodnik planu typu TEMPLATE i określ PARAMETERIZATION FORCED wskazówkę zapytania w przewodniku planu. Aby uzyskać więcej informacji, zobacz Określanie zachowania parametryzacji zapytań przy użyciu przewodników planu.
  • Jeśli MERGE instrukcje są wykonywane często w bazie danych, rozważ ustawienie PARAMETERIZATION opcji w bazie danych na FORCED. Należy zachować ostrożność podczas ustawiania tej opcji. Opcja PARAMETERIZATION jest ustawieniem na poziomie bazy danych i wpływa na sposób przetwarzania wszystkich zapytań względem bazy danych. Aby uzyskać więcej informacji, zobacz Wymuszone parametryzacja.
  • Jako nowsza i łatwiejsza alternatywa do planowania przewodników, rozważ podobną strategię z wskazówkami magazynu zapytań. Aby uzyskać więcej informacji, zobacz Wskazówki dotyczące Query Store.

Najlepsze rozwiązania dotyczące klauzuli TOP

W instrukcji MERGE klauzula TOP określa liczbę lub procent wierszy, których dotyczy problem po sprzężeniu tabeli źródłowej i tabeli docelowej, a po wierszach, które nie kwalifikują się do wstawiania, aktualizowania lub usuwania akcji są usuwane. Klauzula TOP dodatkowo zmniejsza liczbę sprzężonych wierszy do określonej wartości, a akcje wstawiania, aktualizowania lub usuwania są stosowane do pozostałych sprzężonych wierszy w sposób nieurządkowany. Oznacza to, że nie ma kolejności, w której wiersze są dystrybuowane między akcje zdefiniowane w WHEN klauzulach. Na przykład określenie TOP (10) wpływu na 10 wierszy; z tych wierszy 7 może zostać zaktualizowanych i wstawionych 3 lub 1 może zostać usuniętych, 5 zaktualizowanych i 4 wstawionych itd.

Często używa się klauzuli TOP do wykonywania operacji języka manipulowania danymi (DML) na dużej tabeli w partiach. W przypadku używania klauzuli TOP w instrukcji MERGE w tym celu ważne jest, aby zrozumieć następujące implikacje.

  • Może to mieć wpływ na wydajność we/wy.

    Instrukcja MERGE wykonuje pełne skanowanie tabeli zarówno tabel źródłowych, jak i docelowych. Podzielenie operacji na partie zmniejsza liczbę operacji zapisu wykonywanych na partię; jednak każda partia wykonuje pełne skanowanie tabel źródłowych i docelowych. Wynikowe działanie odczytu może mieć wpływ na wydajność zapytania i inne współbieżne działanie w tabelach.

  • Mogą wystąpić nieprawidłowe wyniki.

    Ważne jest, aby upewnić się, że wszystkie kolejne partie są przeznaczone dla nowych wierszy lub niepożądane zachowanie, takie jak niepoprawne wstawianie zduplikowanych wierszy do tabeli docelowej może wystąpić. Może się tak zdarzyć, gdy tabela źródłowa zawiera wiersz, który nie znajdował się w partii docelowej, ale znajdował się w ogólnej tabeli docelowej. Aby zapewnić poprawne wyniki:

    • Użyj klauzuli ON , aby określić, które wiersze źródłowe mają wpływ na istniejące wiersze docelowe i które są naprawdę nowe.
    • Użyj dodatkowego warunku w klauzuli WHEN MATCHED , aby określić, czy wiersz docelowy został już zaktualizowany przez poprzednią partię.
    • Użyj dodatkowego warunku w klauzuli WHEN MATCHED i SET logice, aby sprawdzić, czy ten sam wiersz nie może być dwukrotnie aktualizowany.

Ponieważ klauzula TOP jest stosowana tylko po zastosowaniu tych klauzul, każde wykonanie wstawia jeden rzeczywiście niedopasowany wiersz lub aktualizuje jeden istniejący wiersz.

Najlepsze rozwiązania dotyczące ładowania zbiorczego

Instrukcja MERGE może służyć do wydajnego zbiorczego ładowania danych z pliku danych źródłowych do tabeli docelowej, określając klauzulę OPENROWSET(BULK...) jako źródło tabeli. W ten sposób cały plik jest przetwarzany w jednej partii.

Aby zwiększyć wydajność procesu scalania zbiorczego, zalecamy następujące wskazówki:

  • Utwórz indeks klastrowany w kolumnach sprzężenia w tabeli docelowej.

  • Wyłącz inne indeksy inne niż unikatowe, nieklastrowane w tabeli docelowej podczas ładowania MERGEzbiorczego , włącz je później. Jest to typowe i przydatne w przypadku nocnych operacji zbiorczych danych.

  • ORDER Użyj wskazówek i UNIQUE w klauzuli OPENROWSET(BULK...) , aby określić sposób sortowania pliku danych źródłowych.

    Domyślnie operacja zbiorcza zakłada, że plik danych jest nieurządkowany. Dlatego ważne jest, aby dane źródłowe były sortowane zgodnie z indeksem klastrowanym w tabeli docelowej i że ORDER wskazówka jest używana do wskazania kolejności, aby optymalizator zapytań mógł wygenerować bardziej wydajny plan zapytań. Wskazówki są weryfikowane w czasie wykonywania; Jeśli strumień danych nie jest zgodny z określonymi wskazówkami, zostanie zgłoszony błąd.

Te wytyczne zapewniają, że klucze sprzężenia są unikatowe, a kolejność sortowania danych w pliku źródłowym jest zgodna z tabelą docelową. Wydajność zapytań jest lepsza, ponieważ dodatkowe operacje sortowania nie są niezbędne, a niepotrzebne kopie danych nie są wymagane.

Mierzenie i diagnozowanie wydajności funkcji MERGE

Dostępne są następujące funkcje ułatwiające mierzenie i diagnozowanie wydajności instrukcji MERGE .

  • Użyj licznika stmt scalania w widoku zarządzania dynamicznego sys.dm_exec_query_optimizer_info , aby zwrócić liczbę optymalizacji zapytań, które są przeznaczone dla MERGE instrukcji.
  • Użyj atrybutu merge_action_type w widoku zarządzania dynamicznego sys.dm_exec_plan_attributes, aby zwrócić typ planu wykonywania wyzwalacza używanego w wyniku instrukcji MERGE .
  • Użyj sesji zdarzeń rozszerzonych, aby zebrać dane rozwiązywania problemów dla MERGE instrukcji w taki sam sposób, jak w przypadku innych instrukcji języka manipulowania danymi (DML). Aby uzyskać więcej informacji na temat przeglądu zdarzeń rozszerzonych , zobacz Szybki start: Zdarzenia rozszerzone i Używanie profilera XEvent programu SSMS .

Examples

A. Używanie funkcji MERGE do wykonywania operacji INSERT i UPDATE w tabeli w jednej instrukcji

Typowy scenariusz polega na aktualizowaniu co najmniej jednej kolumny w tabeli, jeśli istnieje pasujący wiersz. Możesz też wstawić dane jako nowy wiersz, jeśli pasujący wiersz nie istnieje. Zazwyczaj wykonujesz dowolny scenariusz, przekazując parametry do procedury składowanej zawierającej odpowiednie UPDATE instrukcje i INSERT . Za pomocą instrukcji MERGE można wykonywać oba zadania w jednej instrukcji. Poniższy przykład pokazuje procedurę przechowywaną w bazie danych AdventureWorks2025, która zawiera zarówno instrukcje, INSERT jak i UPDATE instrukcję. Procedura jest następnie modyfikowana w celu uruchamiania równoważnych operacji przy użyciu pojedynczej MERGE instrukcji.

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. Używanie funkcji MERGE do wykonywania operacji UPDATE i DELETE w tabeli w jednej instrukcji

Poniższy przykład służy MERGE do codziennej aktualizacji tabeli ProductInventory w przykładowej bazie danych AdventureWorks2025, na podstawie zleceń przetwarzanych w tabeli SalesOrderDetail . Kolumna QuantityProductInventory tabeli jest aktualizowana przez odjęcie liczby zamówień złożonych każdego dnia dla każdego produktu w SalesOrderDetail tabeli. Jeśli liczba zamówień produktu spadnie poziom zapasów produktu do wartości 0 lub mniejszej, wiersz dla tego produktu zostanie usunięty z ProductInventory tabeli.

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. Używanie funkcji MERGE do wykonywania operacji UPDATE i INSERT w tabeli docelowej przy użyciu pochodnej tabeli źródłowej

Poniższy przykład służy MERGE do modyfikacji tabeli SalesReason w bazie AdventureWorks2025 poprzez aktualizację lub wstawianie wierszy.

Gdy wartość NewName w tabeli źródłowej jest zgodna z wartością w Name kolumnie tabeli docelowej (SalesReason), kolumna ReasonType zostanie zaktualizowana w tabeli docelowej. Gdy wartość NewName nie jest zgodna, wiersz źródłowy zostanie wstawiony do tabeli docelowej. Tabela źródłowa jest tabelą pochodną, która używa konstruktora wartości tabeli Transact-SQL do określenia wielu wierszy dla tabeli źródłowej. Aby uzyskać więcej informacji na temat używania konstruktora wartości tabeli w tabeli pochodnej, zobacz Konstruktor wartości tabeli (Transact-SQL).

Klauzula może być przydatna OUTPUT do wykonywania zapytań dotyczących wyników instrukcji MERGE , aby uzyskać więcej informacji, zobacz klauzula OUTPUT (Transact-SQL). W przykładzie pokazano również, jak przechowywać wyniki klauzuli OUTPUT w zmiennej tabeli. Następnie podsumujesz wyniki instrukcji MERGE , uruchamiając prostą operację wybierania, która zwraca liczbę wstawionych i zaktualizowanych wierszy.

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

Gdy wartość NewName w tabeli źródłowej jest zgodna z wartością w Name kolumnie tabeli docelowej (SalesReason), kolumna ReasonType zostanie zaktualizowana w tabeli docelowej. Gdy wartość NewName nie jest zgodna, wiersz źródłowy zostanie wstawiony do tabeli docelowej. Tabela źródłowa to tabela pochodna, która służy SELECT ... UNION ALL do określania wielu wierszy dla tabeli źródłowej.

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

D. Wstaw wyniki instrukcji MERGE do innej tabeli

Poniższy przykład przechwytuje dane zwrócone z OUTPUT klauzuli instrukcji MERGE i wstawia te dane do innej tabeli. Oświadczenie MERGE aktualizuje kolumnę Quantity tabeli ProductInventory w bazie AdventureWorks2025, na podstawie zleceń przetwarzanych w tabeli SalesOrderDetail . Przykład przechwytuje zaktualizowane wiersze i wstawia je do innej tabeli używanej do śledzenia zmian spisu.

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. Użyj funkcji MERGE, aby wykonać operację INSERT lub UPDATE w docelowej tabeli brzegowej w grafowej bazie danych

W tym przykładzie utworzysz tabele węzłów Person i City tabelę livesInkrawędzi . Użyj instrukcji MERGElivesIn na krawędzi i wstawisz nowy wiersz, jeśli krawędź jeszcze nie istnieje między elementem a a Person i City. Jeśli krawędź już istnieje, wystarczy zaktualizować atrybut StreetAddress na livesIn krawędzi.

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

Treści powiązane

  • Last updated on