SQL Server MERGE: Основное руководство по оператору MERGE

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

Введение SQL Server MERGE Statement

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

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

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

Если вы используете операторы INSERT, UPDATE и DELETE по отдельности, вам придется создать три отдельных оператора для обновления данных в целевой таблице с соответствующими строками из исходной таблицы.

Однако SQL Server предоставляет оператор MERGE, который позволяет выполнять три действия одновременно. Ниже показан синтаксис оператора MERGE:

MERGE target_table USING source_table
ON merge_condition
WHEN MATCHED
    THEN update_statement
WHEN NOT MATCHED
    THEN insert_statement
WHEN NOT MATCHED BY SOURCE
    THEN DELETE;

Сначала вы указываете целевую и исходную таблицы в предложении MERGE.

Во-вторых, merge_condition определяет, как строки из исходной таблицы сопоставляются со строками из целевой таблицы. Это похоже на условие соединения в предложении соединения. Обычно для сопоставления используются ключевые столбцы — первичный ключ или уникальный ключ.

В-третьих, merge_condition приводит к трем состояниям: СООТВЕТСТВУЕТ, НЕ СООТВЕТСТВУЕТ и НЕ СООТВЕТСТВУЕТ ПО ИСТОЧНИКУ.

MATCHED: это строки, которые соответствуют условию слияния. На диаграмме они показаны синим цветом. Для соответствующих строк вам необходимо обновить столбцы строк в целевой таблице значениями из исходной таблицы.
NOT MATCHED: это строки из исходной таблицы, которые не имеют соответствующих строк в целевой таблице. На диаграмме они показаны оранжевым цветом. В этом случае вам необходимо добавить строки из исходной таблицы в целевую таблицу. Обратите внимание, что NOT MATCHED также известно как NOT MATCHED BY TARGET.
НЕ СООТВЕТСТВУЮТ ИСТОЧНИКУ: это строки в целевой таблице, которые не соответствуют ни одной строке в исходной таблице. Они показаны зеленым на диаграмме. Если вы хотите синхронизировать целевую таблицу с данными из исходной таблицы, вам нужно будет использовать это условие соответствия для удаления строк из целевой таблицы.

Пример оператора MERGE SQL Server

Предположим, у нас есть две таблицы sales.category и sales.category_staging, в которых хранятся продажи по категориям продуктов.

CREATE TABLE sales.category(
    category_id INT PRIMARY KEY,
    category_name VARCHAR(255) NOT NULL,
    amount DECIMAL(10 , 2 )
);
INSERT INTO sales.category(category_id, category_name, amount)
VALUES(1,'Children Bicycles',15000),
   (2,'Comfort Bicycles',25000),
   (3,'Cruisers Bicycles',13000),
   (4,'Cyclocross Bicycles',10000);
CREATE TABLE sales.category_staging(
    category_id INT PRIMARY KEY,
    category_name VARCHAR(255) NOT NULL,
    amount DECIMAL(10 , 2 )
);
INSERT INTO sales.category_staging(category_id, category_name, amount)
VALUES(1,'Children Bicycles',15000),
   (3,'Cruisers Bicycles',13000),
   (4,'Cyclocross Bicycles',20000),
   (5,'Electric Bikes',10000),
   (6,'Mountain Bikes',10000);

Чтобы обновить данные в sales.category(целевая таблица) значениями из sales.category_staging(исходная таблица), используйте следующий оператор MERGE:

MERGE sales.category t 
    USING sales.category_staging s
ON(s.category_id = t.category_id)
WHEN MATCHED
    THEN UPDATE SET 
        t.category_name = s.category_name,
        t.amount = s.amount
WHEN NOT MATCHED BY TARGET 
    THEN INSERT(category_id, category_name, amount)
         VALUES(s.category_id, s.category_name, s.amount)
WHEN NOT MATCHED BY SOURCE 
    THEN DELETE;

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

Во-первых, строки с идентификаторами 1, 3, 4 из таблицы sales.category_staging совпадают со строками из целевой таблицы, поэтому оператор MERGE обновляет значения в столбцах «Название категории» и «Количество» в таблице sales.category.
Во-вторых, строки с идентификаторами 5 и 6 из таблицы sales.category_staging отсутствуют в таблице sales.category, поэтому оператор MERGE вставляет эти строки в целевую таблицу.
В-третьих, строка с идентификатором 2 из таблицы sales.category не существует в таблице sales.sales_staging, поэтому оператор MERGE удаляет эту строку.

В результате слияния данные в таблице sales.category полностью синхронизированы с данными в таблице sales.category_staging.

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