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». На странице курса можно ознакомиться с полной программой, а также посмотреть записи открытых уроков.