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

MERGE (Transact-SQL)

  • Статья

Область применения: SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure базе данных SQL Azure Synapse Analytics в Microsoft Fabric

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

Примечание.

Для сведений MERGE, относящихся к Azure Synapse Analytics, измените выбор версии на Azure Synapse Analytics.

Примечание.

Merge теперь общедоступен в выделенном пуле SQL Synapse с версией 10.0.17829.0 и более поздних версий. Подключитесь к выделенному пулу SQL (ранее — хранилище данных SQL) и запустите его SELECT @@VERSION. Для получения последней версии экземпляра может потребоваться пауза и возобновление.

Совет

Условное поведение, описанное для оператора 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);

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

Синтаксис

Синтаксис для 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:

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

Аргументы

WITH <common_table_expression>

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

TOP ( expression ) [ 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.

[ AS ] table_alias

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

USING <table_source>

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

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

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

[ AS ] table_alias

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

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

ON <merge_search_condition>

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

Внимание

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

WHEN MATCHED THEN <merge_matched>

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

Инструкция MERGE включать не больше двух предложений WHEN MATCHED. Если указаны два предложения, первое предложение должно сопровождаться предложением AND <search_condition>. Для любой строки второе предложение WHEN MATCHED применяется только в том случае, если не применяется первое. Если указаны два предложения WHEN MATCHED, одно должно содержать действие UPDATE, а другое — действие DELETE. При указании 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 ].

WHEN NOT MATCHED BY SOURCE THEN <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, то одно должно указывать действие UPDATE, а другое — действие DELETE. В условии <clause_search_condition> можно ссылаться только на столбцы целевой таблицы.

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

AND <clause_search_condition>

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

<table_hint_limited>

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

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

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

Внимание

Указание 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>, и удовлетворяют дополнительным условиям поиска.

UPDATE SET <set_clause>

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

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

DELETE

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

<merge_not_matched>

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

( column_list )

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

VALUES ( values_list)

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

DEFAULT VALUES

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

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

<search_condition>

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

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

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

Замечания

Должно быть указано по крайней мере одно из трех предложений MATCHED, но они могут быть указаны в любом порядке. В одном предложении MATCHED переменная не может быть обновлена больше одного раза.

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

Чтобы использовать инструкцию MERGE, необходима точка с запятой (;) как признак конца инструкции. Возникает ошибка 10713, если инструкция MERGE выполняется без признака конца конструкции.

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

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

Внимание

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

Рекомендации по 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...WHEN NOT MATCHED INSERT не поддерживается для таблиц с столбцами IDENTITY.
  • Конструктор табличных значений нельзя использовать в предложении USING для исходной таблицы. Используйте SELECT ... UNION ALL для создания производной исходной таблицы с несколькими строками.
  • В следующей таблице описываются поддерживаемые таблицы с разными типами распределения:
MERGE CLAUSE в Azure Synapse Analytics Поддерживаемая таблица распределения TARGET Поддерживаемая таблица распределения SOURCE Комментарий
WHEN MATCHED Все типы распределения Все типы распределения
NOT MATCHED BY TARGET ХЭШ Все типы распределения Используется UPDATE/DELETE FROM...JOIN для синхронизации двух таблиц.
NOT MATCHED BY SOURCE Все типы распределения Все типы распределения

Совет

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

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

Сценарий Комментарий
Случай 1
Использование MERGE в хэш-распределенной таблице TARGET, содержащей вторичные индексы или ограничение UNIQUE.		 — Исправлено в Synapse SQL 10.0.15563.0 и более поздних версиях.
— Если SELECT @@VERSION возвращает более низкую версию, чем 10.0.15563.0, вручную приостанавливайте и возобновляйте работу пула SQL Synapse, чтобы получить это исправление.
— Если исправление еще не применено для вашего пула Synapse SQL, старайтесь не использовать команды MERGE для хэш-распределенных целевых таблиц, содержащих вторичные индексы или ограничения 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

Устранение неполадок

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

  • Несколько столбцов указываются в операции DELETE, UPDATE SET или INSERT в MERGE (не относящиеся к предложению WHEN [NOT] MATCHED)
  • Любой столбец в условии JOIN имеет некластеризованный индекс (NCI)
  • Целевая таблица распределена ХЭШ

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

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

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

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

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

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

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

Примечание.

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

Разрешения

Необходимо разрешение SELECT для исходной таблицы и разрешения INSERT, UPDATE или DELETE для целевой таблицы. Дополнительные сведения см. в разделе "Разрешения" статей об инструкциях SELECT, INSERT, UPDATE и DELETE.

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

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

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

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

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

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

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

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

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

  • В некоторых сценариях, в которых предполагается, что уникальные ключи будут вставляться и обновляться с помощью инструкции MERGE, указание HOLDLOCK позволит предотвратить нарушения, связанные с уникальными ключами. HOLDLOCK — это синоним уровня изоляции транзакций SERIALIZABLE, который не позволяет другим параллельным транзакциям изменять данные, считанные этой транзакцией. SERIALIZABLE является самым надежным уровнем изоляции, но обеспечивает наименьшую степень параллелизма с другими транзакциями, которые сохраняют блокировки на диапазонах данных, чтобы предотвратить вставку или обновление фантомных строк, пока выполняются операции чтения. Дополнительные сведения о HOLDLOCK см. в разделах Указания и SET TRANSACTION ISOLATION LEVEL (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.

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

Примеры

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

Распространен сценарий, при котором один или несколько столбцов в таблице обновляются, если есть строки, соответствующие условиям. Если же таких строк нет, данные вставляются в новую строку. Обычно в обоих случаях параметры передаются в хранимую процедуру с нужными инструкциями UPDATE и INSERT. Инструкция MERGE позволяет реализовать обе эти задачи в одной инструкции. В следующем примере показана хранимая процедура в базе данных AdventureWorks2022, которая содержит инструкцию 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 таблицы в образце базы данных AdventureWorks2022 ежедневно на основе заказов, обработанных в 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';

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

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

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

Предложение OUTPUT может быть полезным для запроса результата инструкции MERGE. Дополнительные сведения см. в разделе Предложение OUTPUT. Также в этом примере показано, как сохранить результаты предложения 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 таблицы в базе данных AdventureWorks2022 на основе заказов, обрабатываемых в 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

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

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

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