Использование оператора MERGE в SQL Server для INSERT и UPDATE

Оператор MERGE в SQL Server позволяет одновременно выполнять операции INSERT, UPDATE и DELETE на одной или нескольких таблицах в зависимости от условий. Это полезно, когда нужно синхронизировать данные между двумя таблицами.

Синтаксис оператора MERGE

MERGE [таблица_назначения] AS target
USING [таблица_источник] AS source
ON [условие_сопоставления]
WHEN MATCHED THEN
    [действие_если_совпадает]
WHEN NOT MATCHED BY TARGET THEN
    [действие_если_не_совпадает_в_целевой_таблице]
WHEN NOT MATCHED BY SOURCE THEN
    [действие_если_не_совпадает_в_таблице_источника];

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

1. WHEN MATCHED

Это условие срабатывает, когда строки из целевой таблицы (target) и строки из таблицы источника (source) совпадают по условию ON. В этом блоке можно выполнять следующие действия:

• UPDATE: Обновляет существующие записи в целевой таблице.

  WHEN MATCHED THEN
      UPDATE SET
          target.Name = source.Name,
          target.Position = source.Position;

2. WHEN NOT MATCHED BY TARGET

Это условие срабатывает, когда строки из таблицы источника (source) не имеют соответствующих строк в целевой таблице (target). Это можно использовать для вставки новых записей.

• INSERT: Вставляет новые записи в целевую таблицу, которые есть в таблице источника, но отсутствуют в целевой таблице.

  WHEN NOT MATCHED BY TARGET THEN
      INSERT (EmployeeID, Name, Position)
      VALUES (source.EmployeeID, source.Name, source.Position);

3. WHEN NOT MATCHED BY SOURCE

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

• DELETE: Удаляет записи из целевой таблицы, которые отсутствуют в таблице источника.

  WHEN NOT MATCHED BY SOURCE THEN
      DELETE;

Примеры использования

Пример 1: Обновление существующих записей и вставка новых

Предположим, у вас есть таблица сотрудников (Employees) и таблица обновлений (EmployeeUpdates), и вам нужно обновить существующих сотрудников и добавить новых.

-- Таблица назначения (сотрудники)
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name NVARCHAR(100),
    Position NVARCHAR(100)
);

-- Таблица источник (обновления сотрудников)
CREATE TABLE EmployeeUpdates (
    EmployeeID INT,
    Name NVARCHAR(100),
    Position NVARCHAR(100)
);

-- Заполнение таблицы источника данными
INSERT INTO EmployeeUpdates (EmployeeID, Name, Position) VALUES
(1, 'Alice', 'Manager'),
(2, 'Bob', 'Developer'),
(3, 'Charlie', 'Analyst');

-- MERGE оператор
MERGE INTO Employees AS target
USING EmployeeUpdates AS source
ON target.EmployeeID = source.EmployeeID
WHEN MATCHED THEN
    UPDATE SET 
        target.Name = source.Name,
        target.Position = source.Position
WHEN NOT MATCHED BY TARGET THEN
    INSERT (EmployeeID, Name, Position)
    VALUES (source.EmployeeID, source.Name, source.Position);

В этом примере:

• Если EmployeeID совпадает в обеих таблицах, происходит обновление данных в Employees.

• Если EmployeeID есть в EmployeeUpdates, но нет в Employees, происходит вставка новой записи.

Пример 2: Удаление старых записей из целевой таблицы

Если вы хотите также удалить записи из таблицы назначения, которые отсутствуют в таблице источника, можно добавить условие WHEN NOT MATCHED BY SOURCE.

-- Таблица назначения (сотрудники)
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name NVARCHAR(100),
    Position NVARCHAR(100)
);

-- Таблица источник (обновления сотрудников)
CREATE TABLE EmployeeUpdates (
    EmployeeID INT,
    Name NVARCHAR(100),
    Position NVARCHAR(100)
);

-- Заполнение таблицы источника данными
INSERT INTO EmployeeUpdates (EmployeeID, Name, Position) VALUES
(1, 'Alice', 'Manager'),
(2, 'Bob', 'Developer');

-- MERGE оператор
MERGE INTO Employees AS target
USING EmployeeUpdates AS source
ON target.EmployeeID = source.EmployeeID
WHEN MATCHED THEN
    UPDATE SET 
        target.Name = source.Name,
        target.Position = source.Position
WHEN NOT MATCHED BY TARGET THEN
    INSERT (EmployeeID, Name, Position)
    VALUES (source.EmployeeID, source

Пример 3: Использования всех условий вместе

Предположим, у вас есть две таблицы: Employees и EmployeeUpdates. Вам нужно обновить существующие записи, вставить новые записи и удалить те записи в Employees, которые отсутствуют в EmployeeUpdates.

MERGE INTO Employees AS target
USING EmployeeUpdates AS source
ON target.EmployeeID = source.EmployeeID
WHEN MATCHED THEN
    UPDATE SET 
        target.Name = source.Name,
        target.Position = source.Position
WHEN NOT MATCHED BY TARGET THEN
    INSERT (EmployeeID, Name, Position)
    VALUES (source.EmployeeID, source.Name, source.Position)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

В этом примере:

• WHEN MATCHED обновляет существующих сотрудников.

• WHEN NOT MATCHED BY TARGET добавляет новых сотрудников.

• WHEN NOT MATCHED BY SOURCE удаляет сотрудников, которых нет в таблице EmployeeUpdates.

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