SCAL (Transact-SQL)

Składnia

[ WITH <common_table_expression> [,...n] ]
MERGE 
    [ TOP ( expression ) [ PERCENT ] ] 
    [ INTO ] <target_table> [ WITH ( <merge_hint> ) ] [ [ AS ] table_alias ]
    USING <table_source> 
    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 . ]
  target_table
}

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

<table_source> ::= 
{
    table_or_view_name [ [ AS ] table_alias ] [ <tablesample_clause> ] 
        [ WITH ( table_hint [ [ , ]...n ] ) ] 
  | rowset_function [ [ AS ] table_alias ] 
        [ ( bulk_column_alias [ ,...n ] ) ] 
  | user_defined_function [ [ AS ] table_alias ]
  | OPENXML <openxml_clause> 
  | derived_table [ AS ] table_alias [ ( column_alias [ ,...n ] ) ] 
  | <joined_table> 
  | <pivoted_table> 
  | <unpivoted_table> 
}

<merge_search_condition> ::=
    <search_condition>

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

<set_clause>::=
SET
  { column_name = { expression | DEFAULT | NULL }
  | { udt_column_name.{ { property_name = expression
                        | field_name = expression }
                        | method_name ( argument [ ,...n ] ) }
    }
  | column_name { .WRITE ( expression , @Offset , @Length ) }
  | @variable = expression
  | @variable = column = expression
  | column_name { += | -= | *= | /= | %= | &= | ^= | |= } expression
  | @variable { += | -= | *= | /= | %= | &= | ^= | |= } expression
  | @variable = column { += | -= | *= | /= | %= | &= | ^= | |= } expression
  } [ ,...n ] 

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

<clause_search_condition> ::=
    <search_condition>

<search condition> ::=
    { [ NOT ] <predicate> | ( <search_condition> ) } 
    [ { AND | OR } [ NOT ] { <predicate> | ( <search_condition> ) } ] 
[ ,...n ] 

<predicate> ::= 
    { expression { = | < > | ! = | > | > = | ! > | < | < = | ! < } expression 
    | string_expression [ NOT ] LIKE string_expression 
  [ ESCAPE 'escape_character' ] 
    | expression [ NOT ] BETWEEN expression AND expression 
    | expression IS [ NOT ] NULL 
    | CONTAINS 
  ( { column | * } , '< contains_search_condition >' ) 
    | FREETEXT ( { column | * } , 'freetext_string' ) 
    | expression [ NOT ] IN ( subquery | expression [ ,...n ] ) 
    | expression { = | < > | ! = | > | > = | ! > | < | < = | ! < } 
  { ALL | SOME | ANY} ( subquery ) 
    | EXISTS ( subquery ) } 

<output_clause>::=
{
    [ OUTPUT <dml_select_list> INTO { @table_variable | output_table }
        [ (column_list) ] ]
    [ OUTPUT <dml_select_list> ]
}

<dml_select_list>::=
    { <column_name> | scalar_expression } 
        [ [AS] column_alias_identifier ] [ ,...n ]

<column_name> ::=
    { DELETED | INSERTED | from_table_name } . { * | column_name }
    | $action

Argumenty

• Z <common_table_expression>
  Określa tymczasowym o nazwie zestaw wyników lub widoku, znane również jako wspólne tabelawyrażenie, zdefiniowany w korespondencji seryjnej instrukcja zakres . zestaw wyników pochodzi od prostych kwerend i odwołują się do korespondencji seryjnej instrukcja.Aby uzyskać więcej informacji, zobacz Z common_table_expression (języka Transact-SQL).

• U góry ( expression ) [%]
  Określa numer lub procent wierszy, których dotyczy.expressionmoże to być liczba lub procent wierszy.Wiersze w GÓRNEJ wyrażenie nie są rozmieszczone w dowolnej kolejności.Aby uzyskać więcej informacji, zobacz U góry (Transact-SQL).

  klauzula TOP jest stosowane po sprzężeniu całą źródłotabela i całej miejsce docelowetabela i sprzężonych wiersze, które nie kwalifikują się do wstawiania, aktualizacji lub usuwania akcja są usuwane. TOP klauzula dalsze zmniejsza liczbę wierszy sprzężonych określona wartość i insert, update lub delete akcje są stosowane do pozostałych wierszy połączonych w sposób nieuporządkowane.Oznacza to, że jest nie zamówienia, w którym wiersze są rozdzielone między akcje zdefiniowane w klauzulach podczas.Na przykład określenie z góry (10) wpływa na wiersze 10; te wiersze 7 mogą być aktualizowane i 3 wstawiony lub 1 mogą zostać usunięte, 5 zaktualizowane i 4 dodaje się i tak dalej.

  Ponieważ korespondencji seryjnej instrukcja wykonuje skanowanie pełne tabela tabeli źródło i miejsce docelowe , wydajność wejścia/wyjścia mogą mieć wpływ podczas modyfikowania dużej tabela , tworząc wiele instancji za pomocą klauzula TOP.W tym scenariuszu jest ważne, aby zapewnić, że wszystkie kolejne instancje miejsce docelowe nowe wiersze.Aby uzyskać więcej informacji, zobacz Optymalizacja wydajności instrukcji korespondencji seryjnej.

• database_name
  Nazwa bazy danych, w którym jest target_table znajduje się.

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

• target_table
  Jest tabela lub widok, którego wiersze danych z <table_source> , są dopasowywane na podstawie <clause_search_condition>.target_tablejest miejsce docelowe wszelkie insert, update lub operacji usuwania określonych przez klauzule podczas scalania instrukcja.

  Jeśli target_table jest widokiem, wszelkie skargi muszą spełniać warunki uaktualniania widoków.Aby uzyskać więcej informacji, zobacz Modyfikowanie danych przy użyciu widoku.

  target_tablenie można tabela zdalna.target_tablenie wszystkie reguły zdefiniowane na nim.

• [ JAK]table_alias
  Alternatywna nazwa służy do odwołania do tabela.

• Za pomocą <table_source>
  Określa źródło danych, dostosowanego z wierszy danych w target_table na podstawie <warunek merge_search>.Wynik to dopasowanie decyduje czynności podejmowane przez klauzule podczas scalania instrukcja.<table_source> może być tabela zdalna lub pochodnych tabela , która uzyskuje dostęp do zdalnych tabel.

  <table_source> może być pochodną tabela , która używa Transact-SQL tabela wartość konstruktora do konstruowania przez określenie wielu wierszy tabela .

  Aby uzyskać więcej informacji na temat składni i argumenty tej klauzula, zobacz Z języka Transact-SQL).

• NA <merge_search_condition>
  Określa warunki, na którym <table_source> połączone z target_table Aby ustalić, gdzie one dopasowania.

  Przestroga

  Jest ważne, aby określić tylko kolumny ztabela miejsce docelowe, które są używane do celów dopasowania. Oznacza to, że określenie kolumn ztabela miejsce docelowe, które są porównywane z odpowiedniej kolumnatabela źródło. Nie należy próbować zwiększyć wydajność kwerendy przez filtrowanie wierszy wtabela miejsce docelowew sprawie klauzulatakich jak określając AND NOT target_table.column_x = value. Wykonanie tej czynności może zwrócić nieoczekiwaną lub nieprawidłowe wyniki.

• GDY następnie DOPASOWANE <merge_matched>
  Określa, że wszystkie wiersze z target_table pasujące wierszy zwracanych przez <table_source> na <merge_search_condition>oraz spełniają wszelkie dodatkowe warunek wyszukiwania, są zaktualizowane lub usunięte zgodnie z <merge_matched> klauzula.

  Scalanie instrukcja może mieć co najwyżej dwa, gdy DOPASOWANE klauzul.Jeśli określono dwie klauzule, a następnie pierwszej klauzula musi towarzyszyć AND <search_condition> klauzula.Dla każdego danego wiersza drugi po DOPASOWANE klauzula jest stosowane tylko, jeśli nie jest pierwszym.Jeżeli są dwa, gdy DOPASOWANE klauzule, a następnie jeden należy określić aktualizacji akcja , oraz jedną należy określić usuwania akcja.Jeśli aktualizacja jest określona w <merge_matched> klauzulai więcej niż jeden wiersz z <table_source>pasuje do wiersza w target_table na podstawie <merge_search_condition>, SQL Server zwraca błąd.Scalanie instrukcja nie można zaktualizować więcej niż jeden raz, tym samym wierszu lub aktualizować i usuwać tego samego wiersza.

• GDY nie SPEŁNIAJĄC [docelowej] następnie <merge_not_matched>
  Określa, że wiersz jest wstawiany target_table dla każdego wiersza zwracana przez <table_source> na <merge_search_condition> nie odpowiada wiersz w target_table, ale spełniają dodatkowy warunek wyszukiwania, jeśli obecnej.Wartości do wstawiania są określane przez <merge_not_matched> klauzula.Scalanie instrukcja może mieć tylko jeden, gdy nie DOPASOWANE klauzula.

• GDY nie DOPASOWANE przez źródło następnie <merge_matched>
  Określa, że wszystkie wiersze z target_table różnią wierszy zwracanych przez <table_source> na <merge_search_condition>, i że spełniają wszelkie dodatkowe warunek wyszukiwania, są zaktualizowane lub usunięte zgodnie z <merge_matched> klauzula.

  Scalanie instrukcja może mieć co najwyżej dwa podczas nie DOPASOWANE przez źródło klauzul.Jeśli określono dwie klauzule, a następnie pierwszej klauzula musi towarzyszyć AND <clause_search_condition> klauzula.Dla każdego danego wiersza po drugim nie DOPASOWANE przez źródło klauzula stosowana jest tylko jeśli nie jest pierwszym.Jeżeli są dwa podczas nie DOPASOWANE przez źródło klauzule, następnie jeden należy określić aktualizacji akcja oraz jeden należy określić usuwania akcja.Można się odwoływać tylko do kolumn ztabela miejsce docelowew <clause_search_condition>.

  Jeśli żadne wiersze są zwracane przez <table_source>, kolumny wtabela źródłonie jest dostępny. Jeśli aktualizacji lub usunięcia akcja określone w <merge_matched>klauzula odwołuje się do kolumn wtabela źródło, zwracany jest błąd 207 (nieprawidłowa kolumna nazwa). Na przykład klauzula WHEN NOT MATCHED BY SOURCE THEN UPDATE SET TargetTable.Col1 = SourceTable.Col1 może spowodować, że instrukcja kończy się niepowodzeniem, ponieważ Col1 w źródło tabela jest niedostępny.

• 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 wskazówki tabela , które są stosowane wtabela miejsce docelowedla każdego insert, update lub delete operacje przeprowadzane przez SERYJNA instrukcja. Słowo kluczowe z i nawiasy są wymagane.

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

  Określanie wskazówka TABLOCK w tabela miejsce docelowe INSERT instrukcja działa tak samo jak określanie wskazówki TABLOCKX.blokada na wyłączność jest pobierana z tabela.FORCESEEK jest określony, jest stosowana do domniemanej wystąpienie miejsce docelowetabela ztabela źródło.

  Przestroga
  Określanie READPAST z, gdy nie SPEŁNIAJĄC [docelowej], a następnie WSTAW może skutkować operacji WSTAWIANIA, które naruszają ograniczenia typu UNIQUE.

• INDEX ( index_val ,...n ] )
  Określa nazwę lub identyfikator jednego lub kilku indeksów w miejsce docelowetabela do wykonywania niejawna łączyć ztabela źródło. Aby uzyskać więcej informacji, zobacz Wskazówki tabel (Transact-SQL).

• <output_clause>
  Zwraca wiersz dla każdego wiersza w target_table , jest zaktualizowany, dodaje się lub usunięty, nie określonej kolejności.Aby uzyskać więcej informacji o argumenty tej klauzula, zobacz Klauzula wyjściowy (Transact-SQL).

• OPTION ( <query_hint> [ ,...n ] )
  Określa, że wskazówek dotyczących optymalizacji są używane do dostosowywania sposób instrukcjaprzetwarzania aparatu bazy danych.Aby uzyskać więcej informacji, zobacz Wskazówki kwerendy (Transact-SQL).

• <merge_matched>
  Określa aktualizację lub usunąć akcja jest stosowana do wszystkich wierszy z target_table różnią wierszy zwracanych przez <table_source> na <merge_search_condition>, i że spełniają wszelkie dodatkowe warunek wyszukiwania.

  • Aktualizacja zestawu <set_clause>
    Określa listę kolumna lub Zmienna nazwy zaktualizowane wtabela miejsce docelowei wartości je zaktualizować.

    Aby uzyskać więcej informacji o argumenty tej klauzula, zobacz UPDATE (Transact-SQL).Ustawienie zmiennej na taką samą wartość jak kolumna nie jest dozwolona.

  • USUŃ
    Określa, że wiersze dopasowywania wierszy w target_table są usuwane.

• <merge_not_matched>
  Określa wartości, aby wstawić dotabela miejsce docelowe.

  • (column_list)
    Jest to lista jednego lub więcej kolumn miejsce docelowe tabela , w którym chcesz wstawić dane.Kolumny musi być określona jako nazwę jednoczęściową, czyli scalanie instrukcja nie powiedzie się.column_listmuszą być ujęte w nawiasy i rozdzielane przecinkami.

  • VALUES ( values_list)
    Jest rozdzielana przecinkami lista stałych, zmiennych lub wyrażeń, które zwracają wartości, aby wstawić dotabela miejsce docelowe. Wyrażenia nie mogą zawierać wykonywanie instrukcja.

  • WARTOŚCI DOMYŚLNE
    Wymusza wstawionego wiersza zawierają wartości domyślne zdefiniowane dla każdej kolumna.

  Aby uzyskać więcej informacji o tej klauzula, zobacz INSERT (Transact-SQL).

• <warunek wyszukiwania>
  Określa warunki wyszukiwania służy do określania, <merge_search_condition> lub <clause_search_condition>.Aby uzyskać więcej informacji o argumenty tej klauzula, zobacz Warunek wyszukiwania (Transact-SQL).

Uwagi

Należy określić co najmniej jeden z trzech klauzul DOPASOWANE, ale można ją określić w dowolnej kolejności.Zmienna nie można zaktualizować więcej niż raz w tej samej DOPASOWANE klauzula.

Wszelkie insert, update lub akcje usuwania określona wtabela miejsce docelowew korespondencji seryjnej instrukcja są ograniczone przez ograniczenia zdefiniowane, włączając kaskadowych ograniczenia więzów integralność . Jeśli IGNORE_DUP_KEY jest zestaw na dla unikatowe indeksy wtabela miejsce docelowe, korespondencji seryjnej zignoruje to ustawienie.

Scalanie instrukcja wymaga średnik (;) jako terminator instrukcja .Błąd 10713 jest wywoływane, gdy korespondencji seryjnej instrukcja jest uruchamiane bez terminator.

Gdy po korespondencji seryjnej, @@ ROWCOUNT (Transact-SQL) zwraca wartość całkowitą liczbę wierszy dodaje, zaktualizowane oraz usunięte klient.

Korespondencji seryjnej jest całkowicie zarezerwowane słowo kluczowe, gdy zgodność bazy danych poziom jest zestaw na 100.Scalanie instrukcja jest dostępnych w ramach zarówno 90 i 100 poziomy zgodności bazy danych; Jednakże słowo kluczowe nie jest całkowicie zarezerwowane podczas zgodności bazy danych poziom jest zestaw na 90.

Implementacja wyzwalacza

Uprawnienia

Wymaga uprawnienie SELECTtabela źródłoi INSERT, UPDATE lub DELETE uprawnienia wtabela miejsce docelowe. Dodatkowe informacje na ten temat można znaleźć w sekcji uprawnienia w Zaznacz, WSTAW, Aktualizacja, i usunąć tematy.

Przykłady

A.Za pomocą korespondencji seryjnej do wykonywania operacji INSERT i UPDATE dla tabela w jednej instrukcja

USE AdventureWorks2008R2;
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.
-- 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 target
    USING (SELECT @UnitMeasureCode, @Name) AS source (UnitMeasureCode, Name)
    ON (target.UnitMeasureCode = source.UnitMeasureCode)
    WHEN MATCHED THEN 
        UPDATE SET Name = source.Name
    WHEN NOT MATCHED THEN   
        INSERT (UnitMeasureCode, Name)
        VALUES (source.UnitMeasureCode, source.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.Za pomocą korespondencji seryjnej do przeprowadzenia aktualizacji i operacji usuwania w tabela w jednej instrukcja

USE AdventureWorks2008R2;
GO
IF OBJECT_ID (N'Production.usp_UpdateInventory', N'P') IS NOT NULL DROP PROCEDURE Production.usp_UpdateInventory;
GO
CREATE PROCEDURE Production.usp_UpdateInventory
    @OrderDate datetime
AS
MERGE Production.ProductInventory AS target
USING (SELECT ProductID, SUM(OrderQty) FROM Sales.SalesOrderDetail AS sod
    JOIN Sales.SalesOrderHeader AS soh
    ON sod.SalesOrderID = soh.SalesOrderID
    AND soh.OrderDate = @OrderDate
    GROUP BY ProductID) AS source (ProductID, OrderQty)
ON (target.ProductID = source.ProductID)
WHEN MATCHED AND target.Quantity - source.OrderQty <= 0
    THEN DELETE
WHEN MATCHED 
    THEN UPDATE SET target.Quantity = target.Quantity - source.OrderQty, 
                    target.ModifiedDate = GETDATE()
OUTPUT $action, Inserted.ProductID, Inserted.Quantity, Inserted.ModifiedDate, Deleted.ProductID,
    Deleted.Quantity, Deleted.ModifiedDate;
GO

EXECUTE Production.usp_UpdateInventory '20030501'

C.Za pomocą korespondencji seryjnej do przeprowadzenia aktualizacji i operacji WSTAWIANIA wtabela miejsce doceloweza pomocą pochodnych źródłotabela

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

MERGE INTO Sales.SalesReason AS Target
USING (VALUES ('Recommendation','Other'), ('Review', 'Marketing'), ('Internet', 'Promotion'))
       AS Source (NewName, NewReasonType)
ON Target.Name = Source.NewName
WHEN MATCHED THEN
    UPDATE SET ReasonType = Source.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.Wstawianie wyniki scalania instrukcja do innej tabela

USE AdventureWorks2008R2;
GO
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
            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

Zobacz także

Odwołanie

Koncepcje