Поделиться через

СЛИЯНИЕ (Transact-SQL)

Относится к:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics (только выделенный SQL-пул)SQL база данных в Microsoft FabricWarehouse в Microsoft Fabric

Инструкция MERGE выполняет операции вставки, обновления или удаления целевой таблицы из результатов соединения с исходной таблицей. Например, можно синхронизировать две таблицы путем вставки, обновления или удаления строк в одной таблице на основании отличий, найденных в другой таблице.

В этой статье приведены различные синтаксисы, аргументы, замечания, разрешения и примеры на основе выбранной версии продукта. Выберите нужную версию продукта из раскрывающегося списка версий.

Note

В хранилище MERGE данных Fabric находится в предварительной версии.

Соглашения о синтаксисе Transact-SQL

Syntax

Синтаксис для SQL Server и Базы данных SQL 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>

Синтаксис для Azure Synapse Analytics, хранилище данных Fabric:

[ 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

С common_table_expression <>

Указывает временный именованный результирующий набор или представление, также называемое общим табличным выражением, которое определено в области инструкции MERGE . Результирующий набор является производным от простого запроса и ссылается на инструкцию MERGE . Дополнительные сведения см. в статье WITH common_table_expression (Transact-SQL).

TOP ( выражение ) [ PERCENT ]

Указывает количество или процент затронутых строк. expression может быть либо числом, либо процентом от числа строк. Строки, TOP на которые ссылается выражение, не упорядочивается в любом порядке. Дополнительные сведения см. в разделе TOP (Transact-SQL).

Предложение TOP применяется после удаления всей исходной таблицы и всего соединения целевой таблицы и присоединенных строк, которые не соответствуют действиям вставки, обновления или удаления. Предложение TOP дополнительно сокращает число присоединенных строк к указанному значению. Эти действия (вставка, обновление или удаление) применяются к оставшимся присоединенным строкам без порядка. То есть нет порядка, в котором строки распределяются между действиями, определенными в WHEN предложениях. Например, указание TOP (10) влияет на 10 строк. Из этих строк 7 могут быть обновлены и 3 вставлены, или 1 может быть удалено, 5 обновлено и 4 вставлено и т. д.

Без фильтров в исходной таблице MERGE оператор может выполнять сканирование таблиц или кластеризованную проверку индекса в исходной таблице, а также сканирование таблицы или кластеризованное сканирование целевой таблицы. Поэтому производительность ввода-вывода иногда влияет даже при использовании TOP предложения для изменения большой таблицы путем создания нескольких пакетов. В этом случае необходимо сделать так, чтобы все последующие пакеты содержали только новые строки.

database_name

Имя базы данных, в которой расположена таблица target_table.

schema_name

Имя схемы, к которой принадлежит таблица target_table.

target_table

Таблица или представление, с которыми выполняется сопоставление строк данных из таблицы <table_source> по условию <clause_search_condition>. target_table — это цель любых операций вставки, обновления или удаления, указанных WHEN в предложениях инструкции MERGE .

Если аргумент таблица target_table является представлением, то все действия, выполняемые с ней, должны удовлетворять условиям для обновления представлений. Дополнительные сведения см. в разделе Изменение данных через представление.

target_table не может быть удаленно расположенной таблицей. target_table не может иметь правил, определенных в нем. target_table не может быть таблицей, оптимизированной для памяти.

Подсказки можно указать как .<merge_hint>

<merge_hint> не поддерживается для Azure Synapse Analytics.

[ КАК ] table_alias

Альтернативное имя для ссылок на таблицу для target_table.

ИСПОЛЬЗОВАНИЕ <table_source>

Указывает источник данных, который сопоставляется со строками данных в таблице target_table на основе условия <merge_search_condition>. Результат этого совпадения определяет действия, выполняемые WHEN предложениями инструкции MERGE . Аргумент <table_source> может быть удаленной таблицей или производной таблицей, которая обращается к удаленным таблицам.

<table_source>Может быть производной таблицей, которая использует конструктор значений таблицы Transact-SQL для создания таблицы, указав несколько строк.

Аргументом <table_source> может быть производная таблица, использующая SELECT ... UNION ALL для построения таблицы путем указания нескольких строк.

[ КАК ] table_alias

Альтернативное имя для ссылок на таблицу для table_source.

Дополнительные сведения о синтаксисе и аргументах этого предложения см. в разделе FROM (Transact-SQL).

НА merge_search_condition <>

Указывает условия, по которым <table_source> соединяется с таблицей target_table для сопоставления.

Caution

Важно указать только те столбцы из целевой таблицы, которые используются для поиска совпадений. Иными словами, необходимо указать столбцы целевой таблицы, которые сравниваются с соответствующим столбцом исходной таблицы. Не пытайтесь повысить производительность запросов, отфильтровав строки в целевой таблице в ON предложении, например указать AND NOT target_table.column_x = value. Это может возвращать непредвиденные и неверные результаты.

ПРИ СОПОСТАВЛЕНИИ MERGE_MATCHED <>

Указывает, что все строки *target_table, соответствующие строкам, возвращаемым <table_source> ON <merge_search_condition>и удовлетворяющим любому дополнительному условию поиска, обновляются или удаляются в соответствии с предложением <merge_matched> .

Оператор MERGE может иметь не более двух WHEN MATCHED предложений. Если указаны два предложения, первое предложение должно сопровождаться предложением AND<search_condition> . Для любой заданной строки второе WHEN MATCHED предложение применяется только в том случае, если первый не является. Если есть два WHEN MATCHED предложения, необходимо указать действие и указать UPDATEDELETE действие. Если UPDATE указано в <merge_matched> предложении, а несколько строк совпадают со строкой <table_source> в target_table на основе <merge_search_condition>, SQL Server возвращает ошибку. Инструкция MERGE не может обновлять одну и ту же строку несколько раз или обновлять и удалять одну и ту же строку.

<WHEN NOT MATCHED [ BY TARGET ] THEN >merge_not_matched>

Указывает, что строка вставляется в target_table для каждой строки, возвращаемой <table_source> ON <merge_search_condition> не соответствующей строке в target_table, но удовлетворяет дополнительному условию поиска, если он присутствует. Значения для вставки указываются с помощью предложения <merge_not_matched>. Инструкция MERGE может содержать только одно WHEN NOT MATCHED [ BY TARGET ] предложение.

ЕСЛИ НЕ СООТВЕТСТВУЕТ ИСТОЧНИКУ, MERGE_MATCHED <>

Указывает, что все строки *target_table, которые не соответствуют строкам, возвращаемым <table_source> ON <merge_search_condition>и которые удовлетворяют любому дополнительному условию поиска, обновляются или удаляются в соответствии с предложением <merge_matched> .

Оператор MERGE может содержать не более двух WHEN NOT MATCHED BY SOURCE предложений. Если указаны два предложения, то первое предложение должно сопровождаться предложением AND<clause_search_condition> . Для любой заданной строки второе WHEN NOT MATCHED BY SOURCE предложение применяется только в том случае, если первый не является. Если есть два WHEN NOT MATCHED BY SOURCE предложения, необходимо указать действие и указать UPDATEDELETE действие. В условии <clause_search_condition> можно ссылаться только на столбцы целевой таблицы.

Если таблица <table_source> не возвращает ни одной строки, доступ к столбцам в исходной таблице не предоставляется. Если операция обновления или удаления, указанная в предложении <merge_matched>, ссылается на столбцы исходной таблицы, то возвращается ошибка 207 (недопустимое имя столбца). Например, предложение WHEN NOT MATCHED BY SOURCE THEN UPDATE SET TargetTable.Col1 = SourceTable.Col1 может привести к сбою инструкции, так как Col1 в исходной таблице недоступно.

И <clause_search_condition>

Указывается любое действительное условие поиска. Дополнительные сведения см. в разделе "Условие поиска" (Transact-SQL).

<table_hint_limited>

Указывает один или несколько подсказок таблицы, применяемых к целевой таблице для каждой из операций вставки, обновления или удаления, выполненных инструкцией MERGE . Ключевое WITH слово и скобки обязательны.

NOLOCK и READUNCOMMITTED не допускаются. Дополнительные сведения о указаниях таблиц см. в статьях "Подсказки таблиц" (Transact-SQL).

Указание TABLOCK указания в таблице, INSERT предназначенной для инструкции, имеет тот же эффект, что и указание TABLOCKX указания. К таблице будет применена монопольная блокировка. Если есть указание FORCESEEK, оно применяется к неявному экземпляру целевой таблицы, соединенной с исходной таблицей.

Caution

Указание READPAST с WHEN NOT MATCHED [ BY TARGET ] THEN INSERT помощью может привести к INSERT операциям, которые нарушают UNIQUE ограничения.

INDEX ( index_val [ ,... n ] )

Указывает имя или идентификатор одного или нескольких индексов целевой таблицы для выполнения неявного соединения с исходной таблицей. Дополнительные сведения см. в статье Указания по таблицам (Transact-SQL).

<output_clause>

Возвращает по одной строке для каждой строки в таблице target_table, в которой выполнена операция обновления, вставки или удаления, без какого-либо определенного порядка. $action можно указать в предложении выходных данных. $action — это столбец типа nvarchar(10), который возвращает одно из трех значений для каждой строки: INSERT, UPDATEили DELETE, в соответствии с действием, выполненным в этой строке. Предложение OUTPUT — это рекомендуемый способ запроса или подсчета строк, затронутых параметром MERGE. Дополнительные сведения о аргументах и поведении этого предложения см. в предложении OUTPUT (Transact-SQL).

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

Указывает, что для настройки способа, которым компонент Database Engine обрабатывает инструкцию, используются подсказки оптимизатора. Дополнительные сведения см. в разделе "Подсказки запросов" (Transact-SQL).

<merge_matched>

Указывает действие обновления или удаления, которое применяется ко всем строкам target_table , которые не соответствуют строкам, возвращаемым <table_source> ON <merge_search_condition>и которые удовлетворяют любому дополнительному условию поиска.

НАБОР <ОБНОВЛЕНИЙ set_clause>

Указывает список имен столбцов или переменных, которые необходимо обновить в целевой таблице, и значений для их обновления.

Дополнительные сведения об аргументах этого предложения см. в разделе UPDATE (Transact-SQL). Присваивание переменной того же значения, что и столбцу, не поддерживается.

DELETE

Указывает, что строки, совпадающие со строками в target_table, удаляются.

<merge_not_matched>

Указываются значения для вставки в целевую таблицу.

( column_list )

Список, состоящий из одного или нескольких столбцов целевой таблицы, в которые вставляются данные. Столбцы должны быть указаны в качестве имени одной части или в противном случае инструкция завершается ошибкой MERGE . Список column_list должен быть заключен в круглые скобки, а его элементы должны разделяться запятыми.

VALUES ( values_list)

Список с разделителями-запятыми, который содержит константы, переменные или выражения, возвращающие значения для вставки в целевую таблицу. Выражения не могут содержать инструкцию EXECUTE .

ЗНАЧЕНИЯ ПО УМОЛЧАНИЮ

Заполняет вставленную строку значениями по умолчанию, определенными для каждого столбца.

Дополнительные сведения об этом предложении см. в разделе INSERT (Transact-SQL).

<search_condition>

Задает условия поиска для указания <merge_search_condition> или <clause_search_condition>. Дополнительные сведения о аргументах этого предложения см. в разделе "Условие поиска" (Transact-SQL).

<шаблон сопоставления графов>

Определяет шаблон сопоставления графов. Дополнительные сведения о аргументах этого предложения см. в разделе MATCH (Transact-SQL).

Remarks

Условное поведение, описанное для MERGE инструкции, лучше всего работает, если две таблицы имеют сложную смесь соответствующих характеристик. Например, для вставки строк, которых не существует, или обновления строки, с которыми есть совпадение. При простом обновлении одной таблицы на основе строк другой таблицы улучшайте производительность и масштабируемость с помощью INSERTинструкций UPDATEи DELETE инструкций. Рассмотрим пример.

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

Необходимо указать хотя бы один из трех MATCHED предложений, но их можно указать в любом порядке. Переменная не может обновляться несколько раз в одном MATCHED предложении.

Любые действия вставки, обновления или удаления, указанные в целевой таблице MERGE инструкцией, ограничены любыми ограничениями, определенными в нем, включая каскадные ограничения целостности. Если IGNORE_DUP_KEY для ON каких-либо уникальных индексов в целевой таблице не MERGE учитывается этот параметр.

Оператору MERGE требуется точка с запятой (;) в качестве конца оператора. Ошибка 10713 возникает при MERGE запуске инструкции без конца.

При использовании после MERGE@@ROWCOUNT (Transact-SQL) возвращает общее количество строк, вставленных, обновленных и удаленных клиенту.

MERGE — это полное зарезервированное ключевое слово, если для уровня совместимости базы данных задано 100 значение или более поздней версии. Инструкция MERGE доступна в 90100 обоих уровнях совместимости базы данных. Однако ключевое слово не полностью зарезервировано, если задан 90уровень совместимости базы данных.

Caution

Не используйте инструкцию MERGE при использовании репликации обновления в очереди. MERGE Триггер обновления в очереди несовместим. Замените инструкцию MERGE оператором INSERT и UPDATE операторами.

Рекомендации по Azure Synapse Analytics

В Azure Synapse Analytics MERGE команда имеет следующие различия по сравнению с SQL Server и Базой данных SQL Azure.

  • Использование MERGE для обновления столбца ключа распространения не поддерживается в сборках старше 10.0.17829.0. Если не удается приостановить или принудительно обновить, используйте инструкцию ANSI UPDATE FROM ... JOIN в качестве обходного решения до версии 10.0.17829.0.
  • MERGE Обновление реализуется как пара удаления и вставки. Число затронутых строк для MERGE обновления включает удаленные и вставленные строки.
  • MERGE...WHEN NOT MATCHED INSERT не поддерживается для таблиц со IDENTITY столбцами.
  • Конструктор значений таблицы не может использоваться в USING предложении для исходной таблицы. Используйте SELECT ... UNION ALL для создания производной исходной таблицы с несколькими строками.
  • В следующей таблице описываются поддерживаемые таблицы с разными типами распределения:
MERGE CLAUSE в Azure Synapse Analytics Поддерживаемая TARGET таблица распространения Поддерживаемая таблица распределения SOURCE Comment
WHEN MATCHED Все типы распределения Все типы распределения
NOT MATCHED BY TARGET HASH Все типы распределения Используется UPDATE/DELETE FROM...JOIN для синхронизации двух таблиц.
NOT MATCHED BY SOURCE Все типы распределения Все типы распределения

Tip

Если вы используете хэш-ключ распределения в качестве JOIN столбца MERGE и выполняете только сравнение равенства, можно опустить ключ распространения из списка столбцов в WHEN MATCHED THEN UPDATE SET предложении, так как это избыточное обновление.

В Azure Synapse Analytics MERGE команда на сборке старше 10.0.17829.0 может при определенных условиях оставить целевую таблицу в несогласованном состоянии, с строками, помещенными в неправильное распределение, что приводит к тому, что последующие запросы возвращают неправильные результаты в некоторых случаях. Эта проблема может возникнуть в 2 случаях:

Scenario Comment
Случай 1
Использование MERGE распределенной TARGET таблицы HASH, содержащей вторичные индексы или UNIQUE ограничение.		 — Исправлено в Synapse SQL 10.0.15563.0 и более поздних версиях.
— Если SELECT @@VERSION возвращает более низкую версию, чем 10.0.15563.0, вручную приостанавливайте и возобновляйте работу пула SQL Synapse, чтобы получить это исправление.
— Пока исправление не будет применено к пулу Synapse SQL, не используйте MERGE команду в HASH распределенных TARGET таблицах с дополнительными индексами или UNIQUE ограничениями.
Случай 2
Использование MERGE для обновления ключевого столбца хэш-распределенной таблицы.		 — Исправлено в Synapse SQL 10.0.17829.0 и более поздних версиях.
— Если SELECT @@VERSION возвращается более низкая версия, чем 10.0.17829.0, вручную приостанавливайте и возобновляйте работу пула SQL Synapse, чтобы получить это исправление.
— Пока исправление не будет применено к пулу Synapse SQL, не используйте MERGE команду для обновления ключевых столбцов дистрибутива.

Обновления в обоих сценариях не восстанавливают таблицы, уже затронутые предыдущим MERGE выполнением. Используйте следующие скрипты, чтобы определить и восстановить все затронутые таблицы вручную.

Чтобы проверить, какие HASH распределенные таблицы в базе данных могут быть обеспокоены (если они используются в ранее упомянутых случаях), выполните следующую инструкцию:

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

Чтобы проверить, влияет ли распределенная HASH таблица MERGE на случай 1 или дело 2, выполните следующие действия, чтобы проверить, имеют ли таблицы строки, приземлились ли строки в неправильном распределении. Если no need for repair возвращается, эта таблица не затрагивается.

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

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

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

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

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

SELECT x
FROM [check_table_1];
GO

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

            EXCEPT

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

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

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

Для восстановления затронутых проблемой таблиц выполните эти инструкции, чтобы скопировать все строки из старой таблицы в новую.

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

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

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

SELECT *
FROM <MERGE_TABLE>;
GO

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

SELECT *
FROM [repair_table_temp];
GO

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

Troubleshooting

В некоторых сценариях инструкция может привести к ошибкеMERGE, CREATE TABLE failed because column <> in table <> exceeds the maximum of 1024 columns. даже если целевая или исходная таблица не содержит 1024 столбцов. Этот сценарий может возникать при выполнении любого из следующих условий:

  • Несколько столбцов указываются в DELETEUPDATE SETINSERT или операции внутри MERGE (не относящиеся к любому WHEN [NOT] MATCHED предложению)
  • Любой столбец в условии JOIN имеет некластеризованный индекс (NCI)
  • Целевая таблица распределена HASH

Если эта ошибка найдена, рекомендуемые обходные пути приведены следующим образом:

  • Удалите некластеризованный индекс (NCI) из JOIN столбцов или присоединение к столбцам без NCI. Если вы позже обновите базовые таблицы, чтобы включить NCI в JOIN столбцы, инструкция MERGE может быть подвержена этой ошибке во время выполнения. Дополнительные сведения см. в разделе DROP INDEX.
  • Вместо инструкций UPDATE, DELETE и MERGE.

Реализация триггера

Для каждого действия вставки, обновления или удаления, указанного MERGE в инструкции, SQL Server запускает все соответствующие AFTER триггеры, определенные в целевой таблице, но не гарантирует, какое действие будет запускать триггеры первым или последним. Триггеры, которые определены для одного и того же действия, реализуются в порядке, указанном пользователем. Дополнительные сведения о настройке порядка выполнения триггеров см. в разделе Указание первого и последнего триггеров.

Если в целевой INSTEAD таблице включен триггер OF, определенный для действия вставки, обновления или удаления, выполняемого MERGE инструкцией, он должен иметь триггер INSTEAD OF для всех действий, указанных в инструкции MERGE .

Если какие-либо INSTEAD триггеры OF или UPDATE OF INSTEADDELETE определены в target_table, операции обновления или удаления не выполняются. Вместо этого запускаются триггеры, которые заполняют таблицы inserted и deleted соответственно.

Если какие-либо INSTEAD триггеры OF INSERT определены в target_table, операция вставки не выполняется. Вместо этого заполняются соответствующие таблицы.

Note

В отличие от отдельных INSERTоператоров UPDATEи DELETE операторов, число строк, отраженных @@ROWCOUNT внутри триггера, может быть выше. Внутри @@ROWCOUNT любого AFTER триггера (независимо от инструкций изменения данных, фиксируемых триггером) будет отражено общее количество строк, затронутых триггером MERGE. Например, если MERGE инструкция вставляет одну строку, обновляет одну строку и удаляет одну строку, @@ROWCOUNT будет три для любого AFTER триггера, даже если триггер объявлен только для INSERT инструкций.

Permissions

Требуется SELECT разрешение на исходную таблицу и INSERTUPDATEразрешения или DELETE разрешения для целевой таблицы. Дополнительные сведения см. в разделе "Разрешения" в статьях SELECT (Transact-SQL), INSERT (Transact-SQL), UPDATE (Transact-SQL), delete (Transact-SQL) .

Рекомендации по использованию индекса

С помощью инструкции MERGE можно заменить отдельные инструкции DML одним оператором. Это может улучшить производительность запросов, так как операции выполняются внутри одной инструкции. Соответственно количество обработок данных в исходных и целевых таблицах снижается. Однако увеличение производительности зависит от наличия правильных индексов, соединений и других факторов.

Чтобы повысить производительность инструкции MERGE , рекомендуется использовать следующие рекомендации по индексу:

  • Создайте индексы для упрощения соединения между источником и целевым объектом MERGE:
    • Создайте индекс для столбцов соединения в исходной таблице с ключами, охватывающими логику соединения с целевой таблицей. Если это возможно, индекс должен быть уникальным.
    • Также создайте индекс в столбцах соединения целевой таблицы. По возможности он должен быть уникальным кластеризованным индексом.
    • Эти два индекса гарантируют, что данные в таблицах сортируются, а уникальность помогает обеспечить производительность для операций сравнения. Производительность запросов увеличивается вследствие того, что оптимизатору запросов не требуется выполнять дополнительную проверку для обнаружения и обновления повторяющихся строк и нет необходимости в выполнении дополнительных операций сортировки.
  • Избегайте таблиц с любой формой индекса columnstore в качестве целевого объекта инструкций MERGE . Как и в случае с любыми UPDATEs, вы можете повысить производительность с помощью индексов columnstore путем обновления промежуточной таблицы rowstore, а затем выполнения пакетной DELETE и INSERT, а не UPDATE или MERGE.

Вопросы параллелизма для инструкции MERGE

С точки зрения блокировки отличается MERGE от дискретных, последовательных INSERTUPDATEи DELETE операторов. MERGE по-прежнему выполняется INSERT, UPDATEи DELETE операции, однако с помощью различных механизмов блокировки. Это может быть более эффективным для записи дискретных INSERTинструкций UPDATEи DELETE инструкций для некоторых потребностей приложения. В большом масштабе MERGE могут возникнуть сложные проблемы с параллелизмом или требуются дополнительные способы устранения неполадок. Таким образом, перед развертыванием в рабочей среде необходимо тщательно протестировать любую MERGE инструкцию.

MERGE операторы подходят для дискретных INSERTопераций UPDATEи DELETE операций (но не ограничиваются) следующими сценариями:

  • Операции извлечения, преобразования и загрузки, включающие большое число строк и выполняемые во время, когда другие параллельные операции не* ожидаются. Если ожидается высокая параллелизм, отдельная INSERTлогика UPDATEи DELETE логика могут выполняться лучше, с меньшими блокировками, чем MERGE оператор.
  • Сложные операции, включающие небольшое число строк и транзакций и которые вряд ли будут выполняться в течение длительного времени.
  • Сложные операции, включающие пользовательские таблицы, в которых индексы могут быть спроектированы для получения оптимальных планов выполнения с избеганием сканирования таблиц и поиска в таблицах в пользу сканирования индексов или в идеальном случае поиска индексов.

Другие рекомендации, связанные с параллелизмом:

  • В некоторых сценариях, когда ожидается, что уникальные ключи будут вставлены и обновлены MERGE, указывая, HOLDLOCK что это позволит предотвратить нарушения уникальных ключей. HOLDLOCK является синонимом SERIALIZABLE уровня изоляции транзакций, который не позволяет другим параллельным транзакциям изменять данные, считываемые этой транзакцией. SERIALIZABLE является самым безопасным уровнем изоляции, но обеспечивает наименьшую параллелизм с другими транзакциями, которые сохраняют блокировки на диапазонах данных, чтобы предотвратить вставку или обновление фантомных строк во время выполнения операций чтения. Дополнительные сведения см. в HOLDLOCKразделе " Подсказки таблиц" и "ЗАДАТЬ УРОВЕНЬ ИЗОЛЯЦИИ ТРАНЗАКЦИЙ" (Transact-SQL).

Рекомендации по использованию соединений

Чтобы повысить производительность инструкции MERGE и убедиться, что получены правильные результаты, рекомендуется использовать следующие рекомендации по присоединению:

  • Укажите только условия поиска в ON <merge_search_condition> предложении, определяющие критерии сопоставления данных в исходных и целевых таблицах. То есть необходимо указать только те столбцы целевой таблицы, которые сравниваются с соответствующими столбцами исходной таблицы.
  • Не включайте сравнения с другими значениями, такими как константа.

Чтобы отфильтровать строки от исходных или целевых таблиц, используйте один из следующих методов.

  • Укажите условие поиска для фильтрации строк в соответствующем WHEN предложении. Например: WHEN NOT MATCHED AND S.EmployeeName LIKE 'S%' THEN INSERT....
  • Определите представление на источнике или цели, возвращающее отфильтрованные строки, и создайте на него ссылку как на исходную или целевую таблицу. Если представление определено на целевой таблице, то все действия, выполняемые с ним, должны удовлетворять условиям для обновления представлений. Дополнительные сведения об обновлении данных с помощью представления см. в разделе "Изменение данных через представление".
  • Используйте предложение WITH <common table expression>, чтобы отфильтровать строки от исходных или целевых таблиц. Этот метод аналогичен указанию дополнительных критериев поиска в ON предложении и может привести к неправильным результатам. Рекомендуется либо не использовать этот метод, либо тщательно протестировать его перед реализацией.

Операция соединения в MERGE инструкции оптимизирована так же, как и соединение в операторе SELECT . То есть при обработке соединения в SQL Server оптимизатор запросов выбирает наиболее эффективный метод обработки из нескольких возможных. Когда источник и цель одного размера и рекомендации по использованию индекса, описанные ранее, применяются к исходным и целевым таблицам, оператор соединения слиянием является наиболее эффективным планом запроса. Это происходит, поскольку обе таблицы просматриваются один раз и сортировка данных не требуется. Когда источник меньше целевой таблицы, предпочтительнее использовать оператор вложенных циклов.

Вы можете принудительно использовать определенное соединение, указав OPTION (<query_hint>) предложение в инструкции MERGE . Рекомендуется не использовать хэш-соединение в качестве указания запроса для MERGE инструкций, так как этот тип соединения не использует индексы.

Рекомендации по использованию параметризации

SELECT INSERT UPDATE DELETEЕсли оператор или оператор выполняются без параметров, оптимизатор запросов SQL Server может выбрать параметризацию инструкции внутри системы. Это значит, что все литеральные значения, содержащиеся в запросе, заменяются параметрами. Например, инструкция INSERT dbo.MyTable (Col1, Col2) VALUES (1, 10)может быть реализована внутренне как INSERT dbo.MyTable (Col1, Col2) VALUES (@p1, @p2). Этот процесс, который называется простой параметризацией, повышает способность реляционного механизма сопоставлять новые инструкции SQL с существующими, ранее скомпилированных планами выполнения. Производительность запросов может быть улучшена, так как частота компиляций запросов и перекомпиляций уменьшается. Оптимизатор запросов не применяет простой процесс параметризации к MERGE операторам. Таким образом, MERGE операторы, содержащие литеральные значения, могут не выполняться и не выполняться отдельно INSERT, UPDATEили DELETE операторы, так как новый план компилируется каждый раз при MERGE выполнении инструкции.

Для увеличения производительности запросов рекомендуется применять следующие рекомендации по использованию параметризации.

  • Параметризируйте все литеральные значения в ON <merge_search_condition> предложении и в WHEN предложениях инструкции MERGE . Например, можно включить инструкцию MERGE в хранимую процедуру, заменив литеральные значения соответствующими входными параметрами.
  • Если инструкцию нельзя параметризовать, создайте структуру плана типа TEMPLATE и укажите в нем указание запроса PARAMETERIZATION FORCED. Дополнительные сведения см. в разделе Указание механизма параметризации запросов с помощью структур плана.
  • Если MERGE инструкции часто выполняются в базе данных, рекомендуется задать PARAMETERIZATION параметр для базы данных FORCED. При установке данного параметра проявляйте осторожность. Параметр PARAMETERIZATION является параметром уровня базы данных и влияет на обработку всех запросов к базе данных. Дополнительные сведения см. в разделе Принудительная параметризация.
  • В качестве более новой и простой альтернативы руководствам по планированию следует рассмотреть аналогичную стратегию с указаниями хранилища запросов. Дополнительные сведения см. в разделе Указания хранилища запросов.

Рекомендации по предложению TOP

MERGE В инструкции TOP предложение указывает количество или процент строк, затронутых после соединения исходной таблицы и целевой таблицы, а также после удаления строк, которые не соответствуют значению действия вставки, обновления или удаления. Предложение TOP дополнительно уменьшает количество присоединенных строк к указанному значению, а действия вставки, обновления или удаления применяются к оставшимся присоединенным строкам в неупорядоченном режиме. То есть нет порядка, в котором строки распределяются между действиями, определенными в WHEN предложениях. Например, указание TOP (10) влияет на 10 строк; из этих строк 7 может быть обновлено и 3 вставлено, или 1 может быть удалено, 5 обновлено и 4 вставлено и т. д.

Обычно предложение используется TOP для выполнения операций языка обработки данных (DML) в большой таблице в пакетах. При использовании TOP предложения в MERGE инструкции для этой цели важно понимать следующие последствия.

  • Производительность ввода-вывода может повлиять.

    Инструкция MERGE выполняет полную проверку таблицы как исходных, так и целевых таблиц. Разделение операции на пакеты уменьшает количество операций записи, выполняемых на каждый пакет; однако каждый пакет выполняет полную проверку таблицы исходных и целевых таблиц. Результирующее действие чтения может повлиять на производительность запроса и другое параллельное действие в таблицах.

  • Это может привести к неверным результатам.

    Важно гарантировать, что все успешные пакеты будут нацелены на новые строки или нежелательное поведение, например неверно вставленные повторяющиеся строки в целевой таблице. Это может произойти при включении в исходную таблицу строки, которая была не в целевом пакете, а в общей целевой таблице. Чтобы гарантировать правильность результатов:

    • ON Используйте предложение, чтобы определить, какие исходные строки влияют на существующие целевые строки и которые действительно новые.
    • Используйте дополнительное условие в WHEN MATCHED предложении, чтобы определить, была ли целевая строка уже обновлена предыдущим пакетом.
    • Используйте дополнительное условие в предложении WHEN MATCHED и SET логике, чтобы проверить, что одна и та же строка не может быть обновлена дважды.

TOP Так как предложение применяется только после применения этих предложений, каждое выполнение либо вставляет одну действительно несоответствуемую строку или обновляет одну существующую строку.

Рекомендации по массовой загрузке

Инструкцию MERGE можно использовать для эффективной массовой загрузки данных из исходного файла данных в целевую таблицу, указав OPENROWSET(BULK...) предложение в качестве источника таблицы. С помощью этого целый файл обрабатывается в одном пакете.

Для улучшения производительности процесса массового слияния приводятся следующие рекомендации.

  • Создайте кластеризованный индекс в столбцах соединения целевой таблицы.

  • Отключите другие некластеризованные индексы в целевой таблице во время массовой загрузки MERGE, включите их после этого. Это распространенный и удобный прием для ночных массовых операций с данными.

  • ORDER UNIQUE Используйте указания в OPENROWSET(BULK...) предложении, чтобы указать способ сортировки исходного файла данных.

    По умолчанию массовая операция считает, что файл данных не упорядочен. Поэтому важно, чтобы исходные данные сортировались в соответствии с кластеризованным индексом в целевой таблице и что ORDER указание используется для указания порядка, чтобы оптимизатор запросов смог создать более эффективный план запроса. Указания проверяются во время выполнения. Если поток данных не соответствует заданным указаниям, возникает ошибка.

Данные руководства гарантируют уникальность ключей соединения и совпадение порядка сортировки данных в исходном файле с целевой таблицей. Производительность запросов увеличивается, так как не требуется дополнительных операций сортировки и необязательных копирований данных.

Измерение и диагностика производительности MERGE

Следующие функции доступны для измерения и диагностики производительности инструкций MERGE .

  • Используйте счетчик stmt слияния в динамическом представлении управления sys.dm_exec_query_optimizer_info, чтобы вернуть количество оптимизаций запросов, которые предназначены для MERGE инструкций.
  • merge_action_type Используйте атрибут в динамическом представлении управления sys.dm_exec_plan_attributes для возврата типа плана выполнения триггера, используемого в результате инструкцииMERGE.
  • Используйте сеанс расширенных событий для сбора данных об устранении неполадок для MERGE инструкции так же, как и для других инструкций языка обработки данных (DML). Дополнительные сведения о расширенных событиях см . в кратком руководстве: расширенные события и использование профилировщика XEvent SSMS.

Examples

A. Использование MERGE для выполнения операций INSERT и UPDATE в таблице в одной инструкции

Распространен сценарий, при котором один или несколько столбцов в таблице обновляются, если есть строки, соответствующие условиям. Если же таких строк нет, данные вставляются в новую строку. Обычно любой сценарий выполняется путем передачи параметров в хранимую процедуру, содержащую соответствующие UPDATE инструкции и INSERT инструкции. С помощью инструкции MERGE можно выполнять обе задачи в одной инструкции. Следующий пример показывает сохранённую процедуру в базе данных AdventureWorks2025, содержащую как INSERT оператор, так и оператор UPDATE . Затем процедура изменяется для выполнения эквивалентных операций с помощью одной MERGE инструкции.

CREATE PROCEDURE dbo.InsertUnitMeasure @UnitMeasureCode NCHAR(3), @Name NVARCHAR(25)
AS
BEGIN
    SET NOCOUNT ON;

    -- Update the row if it exists.
    UPDATE Production.UnitMeasure
    SET Name = @Name
    WHERE UnitMeasureCode = @UnitMeasureCode

    -- Insert the row if the UPDATE statement failed.
    IF (@@ROWCOUNT = 0)
    BEGIN
        INSERT INTO Production.UnitMeasure (
            UnitMeasureCode,
            Name
        )
        VALUES (@UnitMeasureCode, @Name)
    END
END;
GO

-- Test the procedure and return the results.
EXEC InsertUnitMeasure @UnitMeasureCode = 'ABC', @Name = 'Test Value';

SELECT UnitMeasureCode, Name
FROM Production.UnitMeasure
WHERE UnitMeasureCode = 'ABC';
GO

-- Rewrite the procedure to perform the same operations using the
-- MERGE statement.
-- Create a temporary table to hold the updated or inserted values
-- from the OUTPUT clause.
CREATE TABLE #MyTempTable (
    ExistingCode NCHAR(3),
    ExistingName NVARCHAR(50),
    ExistingDate DATETIME,
    ActionTaken NVARCHAR(10),
    NewCode NCHAR(3),
    NewName NVARCHAR(50),
    NewDate DATETIME
);
GO

ALTER PROCEDURE dbo.InsertUnitMeasure @UnitMeasureCode NCHAR(3),
    @Name NVARCHAR(25)
AS
BEGIN
    SET NOCOUNT ON;

    MERGE Production.UnitMeasure AS tgt
    USING (SELECT @UnitMeasureCode, @Name) AS src(UnitMeasureCode, Name)
        ON (tgt.UnitMeasureCode = src.UnitMeasureCode)
    WHEN MATCHED
        THEN
            UPDATE
            SET Name = src.Name
    WHEN NOT MATCHED
        THEN
            INSERT (UnitMeasureCode, Name)
            VALUES (src.UnitMeasureCode, src.Name)
    OUTPUT deleted.*,
        $action,
        inserted.*
    INTO #MyTempTable;
END;
GO

-- Test the procedure and return the results.
EXEC InsertUnitMeasure @UnitMeasureCode = 'ABC', @Name = 'New Test Value';
EXEC InsertUnitMeasure @UnitMeasureCode = 'XYZ', @Name = 'Test Value';
EXEC InsertUnitMeasure @UnitMeasureCode = 'ABC', @Name = 'Another Test Value';

SELECT * FROM #MyTempTable;

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

DROP TABLE #MyTempTable;
GO

CREATE PROCEDURE dbo.InsertUnitMeasure @UnitMeasureCode NCHAR(3),
    @Name NVARCHAR(25)
AS
BEGIN
    SET NOCOUNT ON;

    -- Update the row if it exists.
    UPDATE Production.UnitMeasure
    SET Name = @Name
    WHERE UnitMeasureCode = @UnitMeasureCode

    -- Insert the row if the UPDATE statement failed.
    IF (@@ROWCOUNT = 0)
    BEGIN
        INSERT INTO Production.UnitMeasure (
            UnitMeasureCode,
            Name
        )
        VALUES (@UnitMeasureCode, @Name)
    END
END;
GO

-- Test the procedure and return the results.
EXEC InsertUnitMeasure @UnitMeasureCode = 'ABC', @Name = 'Test Value';

SELECT UnitMeasureCode, Name
FROM Production.UnitMeasure
WHERE UnitMeasureCode = 'ABC';
GO

-- Rewrite the procedure to perform the same operations using the
-- MERGE statement.
ALTER PROCEDURE dbo.InsertUnitMeasure @UnitMeasureCode NCHAR(3),
    @Name NVARCHAR(25)
AS
BEGIN
    SET NOCOUNT ON;

    MERGE Production.UnitMeasure AS tgt
    USING (
        SELECT @UnitMeasureCode,
            @Name
        ) AS src(UnitMeasureCode, Name)
        ON (tgt.UnitMeasureCode = src.UnitMeasureCode)
    WHEN MATCHED
        THEN
            UPDATE SET Name = src.Name
    WHEN NOT MATCHED
        THEN
            INSERT (UnitMeasureCode, Name)
            VALUES (src.UnitMeasureCode, src.Name);
END;
GO

-- Test the procedure and return the results.
EXEC InsertUnitMeasure @UnitMeasureCode = 'ABC', @Name = 'New Test Value';
EXEC InsertUnitMeasure @UnitMeasureCode = 'XYZ', @Name = 'Test Value';
EXEC InsertUnitMeasure @UnitMeasureCode = 'ABC', @Name = 'Another Test Value';

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

B. Использование MERGE для выполнения операций UPDATE и DELETE в таблице в одной инструкции

Следующий пример ежедневно MERGE обновляет ProductInventory таблицу в образцовой базе данных AdventureWorks2025 на основе обработанных SalesOrderDetail в таблице заказов. Столбец Quantity таблицы ProductInventory обновляется путем вычитания количества заказов на каждый продукт, которые размещаются в течение дня в таблице SalesOrderDetail. Если количество заказов на продукт таково, что уровень запасов продукта опускается до нуля или становится еще ниже, то строка этого продукта удаляется из таблицы 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';

CREATE PROCEDURE Production.usp_UpdateInventory @OrderDate DATETIME
AS
MERGE Production.ProductInventory AS tgt
USING (
    SELECT ProductID,
        SUM(OrderQty)
    FROM Sales.SalesOrderDetail AS sod
    INNER JOIN Sales.SalesOrderHeader AS soh
        ON sod.SalesOrderID = soh.SalesOrderID
            AND soh.OrderDate = @OrderDate
    GROUP BY ProductID
    ) AS src(ProductID, OrderQty)
    ON (tgt.ProductID = src.ProductID)
WHEN MATCHED
    AND tgt.Quantity - src.OrderQty <= 0
    THEN
        DELETE
WHEN MATCHED
    THEN
        UPDATE
        SET tgt.Quantity = tgt.Quantity - src.OrderQty,
            tgt.ModifiedDate = GETDATE();
GO

EXECUTE Production.usp_UpdateInventory '20030501';

C. Использование MERGE для выполнения операций UPDATE и INSERT в целевой таблице с помощью производной исходной таблицы

В следующем примере используется MERGE модификация SalesReason таблицы в базе данных AdventureWorks2025 путём обновления или вставки строк.

Если значение NewName в исходной таблице соответствует значению в столбце Name целевой таблицы (SalesReason), то в целевой таблице обновляется столбец ReasonType. Если значение NewName не совпадает со значением в целевой таблице, исходная строка вставляется в целевую таблицу. В качестве исходной используется производная таблица, в которой конструктор табличных значений Transact-SQL используется для указания нескольких строк исходной таблицы. Дополнительные сведения об использовании конструктора табличных значений в производной таблице см. в разделе Конструктор табличных значений (Transact-SQL).

Предложение OUTPUT может быть полезно для запроса результатов инструкций MERGE , дополнительные сведения см. в предложении OUTPUT (Transact-SQL). В примере также показано, как хранить результаты OUTPUT предложения в табличной переменной. Затем вы суммируете результаты инструкции MERGE , выполнив простую операцию выбора, которая возвращает количество вставленных и обновленных строк.

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

Если значение NewName в исходной таблице соответствует значению в столбце Name целевой таблицы (SalesReason), то в целевой таблице обновляется столбец ReasonType. Если значение NewName не совпадает со значением в целевой таблице, исходная строка вставляется в целевую таблицу. Исходной таблицей является производная таблица, в которой используется SELECT ... UNION ALL для указания нескольких строк исходной таблицы.

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. Вставка результатов инструкции MERGE в другую таблицу

В следующем примере данные записываются из OUTPUT предложения инструкции MERGE и вставляются в другую таблицу. Оператор MERGE обновляет Quantity столбец ProductInventory таблицы в базе данных AdventureWorks2025 на основе порядков, обрабатываемых в SalesOrderDetail таблице. Этот пример отслеживает обновленные строки и вставляет их в другую таблицу, в которой отслеживаются все изменения инвентарных запасов.

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. Использование MERGE для выполнения инструкций INSERT или UPDATE в целевой пограничной таблице в базе данных графа

В этом примере мы создадим таблицы узлов Person и City, а также таблицу ребер livesIn. Вы используете инструкцию MERGE на livesIn границе и вставляете новую строку, если край еще не существует между и PersonCity. Если ребро уже существует, мы только обновим атрибут StreetAddress в таблице ребер 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

Связанный контент

  • Last updated on