MERGE + OUTPUT: Upsert с логированием без триггеров

Привет, Хабр!

Сегодня поговорим про MERGE в MS SQL Server. Не просто MERGE, а MERGE с OUTPUT — как обновлять данные, вставлять новые и одновременно логировать изменения.

Оператор MERGE позволяет объединить INSERT, UPDATE и DELETE. Клаузу OUTPUT можно прикрутить, чтобы получить, что именно поменялось — с деталями: было, стало, когда, зачем и кто виноват.

Пример. Есть у нас:

• таблица Products — основной справочник

• UpdatedProducts — новые данные

• ProductChangesLog — журнал изменений

Создаём таблицы:

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName NVARCHAR(100),
    Price DECIMAL(10,2),
    LastUpdated DATETIME DEFAULT GETDATE()
);

CREATE TABLE UpdatedProducts (
    ProductID INT PRIMARY KEY,
    ProductName NVARCHAR(100),
    Price DECIMAL(10,2)
);

CREATE TABLE ProductChangesLog (
    LogID INT IDENTITY(1,1) PRIMARY KEY,
    Action NVARCHAR(10),
    ProductID INT,
    OldProductName NVARCHAR(100),
    NewProductName NVARCHAR(100),
    OldPrice DECIMAL(10,2),
    NewPrice DECIMAL(10,2),
    ChangeDate DATETIME DEFAULT GETDATE()
);

Теперь сам MERGE с логированием:

MERGE INTO Products AS target
USING UpdatedProducts AS source
ON target.ProductID = source.ProductID
WHEN MATCHED AND 
    (target.ProductName <> source.ProductName OR target.Price <> source.Price) THEN
    UPDATE SET target.ProductName = source.ProductName,
               target.Price = source.Price,
               target.LastUpdated = GETDATE()
WHEN NOT MATCHED BY TARGET THEN
    INSERT (ProductID, ProductName, Price, LastUpdated)
    VALUES (source.ProductID, source.ProductName, source.Price, GETDATE())
WHEN NOT MATCHED BY SOURCE THEN
    DELETE
OUTPUT $action AS Action,
       COALESCE(INSERTED.ProductID, DELETED.ProductID) AS ProductID,
       DELETED.ProductName AS OldProductName,
       INSERTED.ProductName AS NewProductName,
       DELETED.Price AS OldPrice,
       INSERTED.Price AS NewPrice,
       GETDATE() AS ChangeDate
INTO ProductChangesLog (Action, ProductID, OldProductName, NewProductName, OldPrice, NewPrice, ChangeDate);

$action — тип операции, COALESCE — берёт ProductID из доступных данных, а OUTPUT ... INTO — сразу пишет лог в таблицу, без необходимости использовать AFTER-триггеры.

А теперь добавим пример, когда изменения логируются, но в таблицу пишется не только факт, но и автор изменения:

ALTER TABLE ProductChangesLog ADD ChangedBy NVARCHAR(100);

-- допустим, мы знаем пользователя из приложения через SESSION_CONTEXT
EXEC sp_set_session_context 'username', 'admin_user';

MERGE INTO Products AS target
USING UpdatedProducts AS source
ON target.ProductID = source.ProductID
WHEN MATCHED AND 
    (target.ProductName <> source.ProductName OR target.Price <> source.Price) THEN
    UPDATE SET target.ProductName = source.ProductName,
               target.Price = source.Price,
               target.LastUpdated = GETDATE()
WHEN NOT MATCHED BY TARGET THEN
    INSERT (ProductID, ProductName, Price, LastUpdated)
    VALUES (source.ProductID, source.ProductName, source.Price, GETDATE())
OUTPUT $action AS Action,
       COALESCE(INSERTED.ProductID, DELETED.ProductID),
       DELETED.ProductName,
       INSERTED.ProductName,
       DELETED.Price,
       INSERTED.Price,
       GETDATE(),
       SESSION_CONTEXT(N'username')
INTO ProductChangesLog (Action, ProductID, OldProductName, NewProductName, OldPrice, NewPrice, ChangeDate, ChangedBy);

Возможные проблемы

Deadlock. Если несколько MERGE-ов одновременно — может заклинить. SQL не гарантирует порядок блокировок. Решения: обрабатывать пачками, ставить индексы, не держать транзакции открытыми.

Multiple match. Если в source дубли по ключу — получите ошибку. Проверяйте уникальность или агрегируйте:

WITH DistinctSource AS (
  SELECT ProductID, MAX(ProductName) AS ProductName, MAX(Price) AS Price
  FROM UpdatedProducts
  GROUP BY ProductID
)
MERGE Products USING DistinctSource ...

Можно завернуть в хранимку:

CREATE PROCEDURE dbo.UpsertProducts
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;
    BEGIN TRAN;
    BEGIN TRY
        -- MERGE как выше
        COMMIT;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK;
        THROW;
    END CATCH
END

Для больших объёмов — батчи:

WHILE 1=1
BEGIN
  WITH Batch AS (
    SELECT TOP (1000) * FROM UpdatedProducts ORDER BY ProductID
  )
  MERGE Products USING Batch ...
  IF @@ROWCOUNT = 0 BREAK;
END

А если хочется писать логи не в таблицу, а получать наружу — например, в логи приложения или JSON ответ:

DECLARE @changes TABLE (
    Action NVARCHAR(10),
    ProductID INT,
    OldName NVARCHAR(100),
    NewName NVARCHAR(100)
);

MERGE Products AS target
USING UpdatedProducts AS source
ON target.ProductID = source.ProductID
WHEN MATCHED THEN
    UPDATE SET ProductName = source.ProductName
OUTPUT $action, DELETED.ProductID, DELETED.ProductName, INSERTED.ProductName
INTO @changes;

SELECT * FROM @changes;

---

Заключение

MERGE с OUTPUT — достойный инструмент. Можно в одном выражении обновлять данные, вставлять новые и логировать изменения. Без триггеров и без костылей. Но требует аккуратности: индексируйте, не забывайте про уникальность и следите за транзакциями.

Статья подготовлена в преддверии старта онлайн-курса "MS SQL Server Developer". На странице курса можно ознакомиться с полной программой, а также посмотреть записи открытых уроков.