Статья
04/01/2012

Добавление, обновление и удаление данных с помощью инструкции MERGE

В SQL Server 2008 операции INSERT, UPDATE и DELETE можно выполнить в одной инструкции MERGE. Инструкция MERGE позволяет соединить источник данных с целевой таблицей или представлением, а затем выполнить множество действий по отношению к целевому объекту в зависимости от результатов такого соединения. Например, инструкцию MERGE можно использовать для выполнения следующих операций.

Для условной вставки или обновления строк в целевой таблице.

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

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

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

В предложении MERGE задается таблица или представление, которые являются целью операций INSERT, UPDATE или DELETE.
В предложении USING задается источник данных, который будет соединен с целью.
В предложении ON указываются условия соединения, которые определяют, где должны совпадать источник и цель.
Предложения WHEN (WHEN MATCHED, WHEN NOT MATCHED BY TARGET и WHEN NOT MATCHED BY SOURCE) указывают действия, предпринимаемые на основе результатов предложения ON и любых дополнительных критериев поиска, указанных в предложениях WHEN.
Предложение OUTPUT возвращает строку для каждой обновленной, вставленной или удаленной строки в цели.

Полную информацию о синтаксисе и правилах см. в разделе MERGE (Transact-SQL).

Указание исходного и целевого условий поиска

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

Строки в источнике сопоставляются со строками в цели на основе предиката соединения, указанного в предложении ON. Результатом является объединенный входной поток. На одну входную строку выполняется одна операция вставки, обновления и удаления. В зависимости от предложений WHEN, указанных в инструкции, входная строка может быть одной из перечисленных ниже.

Совпадающая пара состоит из одной строки цели и одной строки источника. Это результат для предложения WHEN MATCHED.
Строка источника не имеет соответствующей строки в цели. Это результат предложения WHEN NOT MATCHED BY TARGET.
Строка цели не имеет соответствующей строки в источнике. Это результат предложения WHEN NOT MATCHED BY SOURCE.

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

USE tempdb;
GO
CREATE TABLE dbo.Target(EmployeeID int, EmployeeName varchar(10), 
     CONSTRAINT Target_PK PRIMARY KEY(EmployeeID));
CREATE TABLE dbo.Source(EmployeeID int, EmployeeName varchar(10), 
     CONSTRAINT Source_PK PRIMARY KEY(EmployeeID));
GO
INSERT dbo.Target(EmployeeID, EmployeeName) VALUES(100, 'Mary');
INSERT dbo.Target(EmployeeID, EmployeeName) VALUES(101, 'Sara');
INSERT dbo.Target(EmployeeID, EmployeeName) VALUES(102, 'Stefano');

GO
INSERT dbo.Source(EmployeeID, EmployeeName) Values(103, 'Bob');
INSERT dbo.Source(EmployeeID, EmployeeName) Values(104, 'Steve');
GO

В следующей таблице перечислены возможные типы соединений и реализация каждого типа определена оптимизатором запросов. В таблице также показан итоговый входной поток для примеров исходных и целевых таблиц, когда критерием поиска совпадений исходных и целевых данных является Source.EmployeeID = Target.EmployeeID.

Тип соединения	Реализация	Пример результатов входного потока
INNER JOIN	Предложение WHEN MATCHED — единственное указанное предложение WHEN.	SrcEmpID SrcName TrgEmpID TrgName -------- ------- -------- ------- NULL NULL NULL NULL
LEFT OUTER JOIN	Предложение WHEN NOT MATCHED BY TARGET указывается, предложение WHEN NOT MATCHED BY SOURCE — нет. WHEN MATCHED может как указываться, так и не указываться.	SrcEmpID SrcName TrgEmpID TrgName -------- ------- -------- -------100 Mary NULL NULL 101 Sara NULL NULL 102 Stefano NULL NULL
RIGHT OUTER JOIN	Предложения WHEN MATCHED и WHEN NOT MATCHED BY SOURCE указываются, предложение WHEN NOT MATCHED BY TARGET — нет.	SrcEmpID SrcName TrgEmpID TrgName -------- ------- -------- -------NULL NULL 103 Bob NULL NULL 104 Steve
FULL OUTER JOIN	Указываются предложения WHEN NOT MATCHED BY TARGET и WHEN NOT MATCHED BY SOURCE. WHEN MATCHED может как указываться, так и не указываться.	SrcEmpID SrcName TrgEmpID TrgName -------- ------- -------- -------100 Mary NULL NULL 101 Sara NULL NULL 102 Stefano NULL NULL NULL NULL 103 Bob NULL NULL 104 Steve
ANTI SEMI JOIN	WHEN NOT MATCHED BY SOURCE — единственное указанное предложение WHEN.	TrgEmpID TrgName -------- ------- 100 Mary 101 Sara 102 Stefano

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

Вставить строки из исходной таблицы, если в целевой таблице нет идентификатора сотрудника и исходное имя сотрудника начинается с буквы «S».
Удалить строки из целевой таблицы, если целевое имя сотрудника начинается с буквы «S» и в исходной таблице нет идентификатора сотрудника.

Для выполнения этих действий требуются следующие предложения WHEN:

WHEN NOT MATCHED BY TARGET THEN INSERT
WHEN NOT MATCHED BY SOURCE THEN DELETE

Как описано в предыдущей таблице, если указываются оба предложения WHEN NOT MATCHED, то итоговый входной поток является полным внешним соединением исходных и целевых таблиц. Теперь, когда результаты входного потока известны, рассмотрите применение к нему операций вставки, обновления и удаления.

Как было указано ранее, предложения WHEN указывают на действия, которые нужно предпринять на основе результатов предложения ON и любых дополнительных критериев поиска, указанных в предложениях WHEN. Во многих случаях условия поиска, указанные в предложении ON, создают требуемый входной поток. Однако в сценарии из этого примера операции вставки и удаления требуют дополнительной фильтрации для ограничения затронутых строк, которые содержат имя сотрудника, начинающееся с буквы «S». В следующем примере условия фильтрации применяются к предложениям WHEN NOT MATCHED BY TARGET и WHEN NOT MATCHED BY SOURCE. Выходные данные от инструкции показывают, что ожидаемые от входного потока строки верны, вставлены или удалены.

-- MERGE statement with the join conditions specified correctly.
USE tempdb;
GO
BEGIN TRAN;
MERGE Target AS T
USING Source AS S
ON (T.EmployeeID = S.EmployeeID) 
WHEN NOT MATCHED BY TARGET AND S.EmployeeName LIKE 'S%' 
    THEN INSERT(EmployeeID, EmployeeName) VALUES(S.EmployeeID, S.EmployeeName)
WHEN MATCHED 
    THEN UPDATE SET T.EmployeeName = S.EmployeeName
WHEN NOT MATCHED BY SOURCE AND T.EmployeeName LIKE 'S%'
    THEN DELETE 
OUTPUT $action, inserted.*, deleted.*;
ROLLBACK TRAN;
GO

Ниже приведены результаты предложения OUTPUT.

$action EmployeeID EmployeeName EmployeeID EmployeeName

--------- ----------- ------------ ----------- ------------

DELETE NULL NULL 101 Sara

DELETE NULL NULL 102 Stefano

INSERT 104 Steve NULL NULL

(Обработано строк: 3)

Чтобы увеличить производительность запросов, можно уменьшить число строк во входном потоке на начальном этапе процесса с помощью дополнительного критерия поиска для предложения ON (например, ON Source.EmployeeID = Target.EmployeeID AND EmployeeName LIKE 'S%'). Однако это может привести к непредвиденным и неверным результатам. Так как дополнительные условия поиска, указанные в предложении ON, не используются для сопоставления исходных и целевых данных, они могут быть использованы неправильно.

В следующем примере показано возникновение неверных результатов. Условие поиска для сравнения исходных и целевых таблиц и дополнительное условие поиска для строк фильтрации указаны в предложении ON. Так как для сопоставления источника и цели не требуется дополнительное условие поиска, операции вставки и удаления применяются ко всем входным строкам. В результате условие фильтрации EmployeeName LIKE 'S%' не учитывается. При выполнении инструкции выходные данные таблиц inserted и deleted показывают, что две строки изменены неверно: имя «Mary» неверно удалено из целевой таблицы, а имя «Bob» вставлено неправильно.

-- MERGE statement with join conditions that produce unexpected results.
USE tempdb;
GO
BEGIN TRAN;
MERGE Target AS T
USING Source AS S
ON (T.EmployeeID = S.EmployeeID AND T.EmployeeName LIKE 'S%' 
    AND S.EmployeeName LIKE 'S%' )
WHEN NOT MATCHED BY TARGET
    THEN INSERT(EmployeeID, EmployeeName) VALUES(S.EmployeeID, S.EmployeeName)
WHEN MATCHED 
    THEN UPDATE SET T.EmployeeName = S.EmployeeName
WHEN NOT MATCHED BY SOURCE
    THEN DELETE
OUTPUT $action, Inserted.*, Deleted.*;
ROLLBACK TRAN;
GO

Ниже приведены результаты предложения OUTPUT.

$action EmployeeID EmployeeName EmployeeID EmployeeName

--------- ----------- ------------ ----------- ------------

DELETE NULL NULL 100 Mary

DELETE NULL NULL 101 Sara

DELETE NULL NULL 102 Stefano

INSERT 103 Bob NULL NULL

INSERT 104 Steve NULL NULL

(Обработано строк: 5)

Примеры

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

Предположим, что в базе данных хранилища данных существует таблица FactBuyingHabits, в которой отслеживаются последние даты покупки каждым клиентом определенных продуктов. Во второй таблице базы данных OLTP Purchases записываются покупки, совершенные в течение данной недели. Каждую неделю необходимо добавлять строки о продуктах, которых определенные клиенты ни разу не покупали, из таблицы Purchases в таблицу FactBuyingHabits. Что касается строк о повторной покупке клиентами товаров, то необходимо просто обновить дату покупки в таблице FactBuyingHabits. Две эти операции, INSERT и UPDATE, могут быть выполнены в рамках одной инструкции MERGE.

В следующем примере сначала создаются таблицы Purchases и FactBuyingHabits, а затем производится их заполнение данными, которые будут использованы в качестве образца. Производительность инструкций MERGE улучшается, если на ключе соединения создаются индексы UNIQUE, поэтому кластеризованные индексы создаются путем создания ограничения PRIMARY KEY для столбцов ProductID в обеих таблицах.

В данном примере в таблице Purchases содержатся записи о покупках за неделю начиная с 21 августа 2006 г. В таблице FactBuyingHabits содержатся записи о покупках за предыдущую неделю. Обычно в такой таблице содержались бы строки за гораздо больший период времени.

USE AdventureWorks2008R2;
GO
IF OBJECT_ID (N'dbo.Purchases', N'U') IS NOT NULL 
    DROP TABLE dbo.Purchases;
GO
CREATE TABLE dbo.Purchases (
    ProductID int, CustomerID int, PurchaseDate datetime, 
    CONSTRAINT PK_PurchProdID PRIMARY KEY(ProductID,CustomerID));
GO
INSERT INTO dbo.Purchases VALUES(707, 11794, '20060821'),
(707, 15160, '20060825'),(708, 18529, '20060821'),
(711, 11794, '20060821'),(711, 19585, '20060822'),
(712, 14680, '20060825'),(712, 21524, '20060825'),
(712, 19072, '20060821'),(870, 15160, '20060823'),
(870, 11927, '20060824'),(870, 18749, '20060825');
GO
IF OBJECT_ID (N'dbo.FactBuyingHabits', N'U') IS NOT NULL 
    DROP TABLE dbo.FactBuyingHabits;
GO
CREATE TABLE dbo.FactBuyingHabits (
    ProductID int, CustomerID int, LastPurchaseDate datetime, 
    CONSTRAINT PK_FactProdID PRIMARY KEY(ProductID,CustomerID));
GO
INSERT INTO dbo.FactBuyingHabits VALUES(707, 11794, '20060814'),
(707, 18178, '20060818'),(864, 14114, '20060818'),
(866, 13350, '20060818'),(866, 20201, '20060815'),
(867, 20201, '20060814'),(869, 19893, '20060815'),
(870, 17151, '20060818'),(870, 15160, '20060817'),
(871, 21717, '20060817'),(871, 21163, '20060815'),
(871, 13350, '20060815'),(873, 23381, '20060815');
GO

Сейчас в таблицах содержатся следующие данные.

dbo.Purchases

ProductID CustomerID PurchaseDate

----------- ----------- -----------------------

707 11794 2006-08-20 00:00:00.000

707 15160 2006-08-25 00:00:00.000

708 18529 2006-08-21 00:00:00.000

711 11794 2006-08-20 00:00:00.000

711 19585 2006-08-22 00:00:00.000

712 14680 2006-08-26 00:00:00.000

712 21524 2006-08-26 00:00:00.000

712 19072 2006-08-20 00:00:00.000

870 15160 2006-08-23 00:00:00.000

870 11927 2006-08-24 00:00:00.000

870 18749 2006-08-25 00:00:00.000

dbo.FactBuyingHabits

ProductID CustomerID LastPurchaseDate

----------- ----------- -----------------------

707 11794 2006-08-14 00:00:00.000

707 18178 2006-08-18 00:00:00.000

864 14114 2006-08-18 00:00:00.000

866 13350 2006-08-18 00:00:00.000

866 20201 2006-08-15 00:00:00.000

867 20201 2006-08-14 00:00:00.000

869 19893 2006-08-15 00:00:00.000

870 17151 2006-08-18 00:00:00.000

870 15160 2006-08-17 00:00:00.000

871 21717 2006-08-17 00:00:00.000

871 21163 2006-08-15 00:00:00.000

871 13350 2006-08-15 00:00:00.000

873 23381 2006-08-15 00:00:00.000

Необходимо заметить, что в обеих таблицах существуют две общие строки типа продукт-клиент. Клиент 11794 купил продукт 707 в течение текущей недели, а также в течение предыдущей. То же самое верно для клиента 15160 и продукта 870. При работе с этими строками таблица FactBuyingHabits будет обновлена датами, зарегистрированными для этих покупок в таблице Purchases, с помощью предложения WHEN MATCHED THEN. Все остальные строки будут добавлены в таблицу FactBuyingHabits с помощью предложения WHEN NOT MATCHED THEN.

MERGE dbo.FactBuyingHabits AS Target
USING (SELECT CustomerID, ProductID, PurchaseDate FROM dbo.Purchases) AS Source
ON (Target.ProductID = Source.ProductID AND Target.CustomerID = Source.CustomerID)
WHEN MATCHED THEN
    UPDATE SET Target.LastPurchaseDate = Source.PurchaseDate
WHEN NOT MATCHED BY TARGET THEN
    INSERT (CustomerID, ProductID, LastPurchaseDate)
    VALUES (Source.CustomerID, Source.ProductID, Source.PurchaseDate)
OUTPUT $action, Inserted.*, Deleted.*;

Б. Выполнение операций UPDATE и DELETE

В следующем примере инструкция MERGE используется для ежедневного обновления таблицы ProductInventory в образце базы данных База данных AdventureWorks2008R2, основываясь на заказах, обрабатываемых в таблице SalesOrderDetail. Столбец Quantity таблицы ProductInventory обновляется с помощью инструкции MERGE методом вычитания числа заказов, которые ежедневно поступают на каждый продукт. Если число заказов сводит количество продукта к нулю или ниже, то строка этого продукта удаляется из таблицы ProductInventory. Заметим, что по столбцу ProductID исходной таблицы выполняется статистическая обработка. Если этого не сделать, существует вероятность, что в исходной и целевой таблицах совпадет больше одной строки ProductID, что вызовет завершение инструкции MERGE с ошибкой.

USE AdventureWorks2008R2;
GO
IF OBJECT_ID (N'Production.usp_UpdateInventory', N'P') IS NOT NULL DROP PROCEDURE Production.usp_UpdateInventory;
GO
CREATE PROCEDURE Production.usp_UpdateInventory
    @OrderDate datetime
AS
MERGE Production.ProductInventory AS target
USING (SELECT ProductID, SUM(OrderQty) FROM Sales.SalesOrderDetail AS sod
    JOIN Sales.SalesOrderHeader AS soh
    ON sod.SalesOrderID = soh.SalesOrderID
    AND soh.OrderDate = @OrderDate
    GROUP BY ProductID) AS source (ProductID, OrderQty)
ON (target.ProductID = source.ProductID)
WHEN MATCHED AND target.Quantity - source.OrderQty <= 0
    THEN DELETE
WHEN MATCHED 
    THEN UPDATE SET target.Quantity = target.Quantity - source.OrderQty, 
                    target.ModifiedDate = GETDATE()
OUTPUT $action, Inserted.ProductID, Inserted.Quantity, Inserted.ModifiedDate, Deleted.ProductID,
    Deleted.Quantity, Deleted.ModifiedDate;
GO

EXECUTE Production.usp_UpdateInventory '20030501'

В. Выполнение операций INSERT, UPDATE и DELETE

В следующем примере инструкция MERGE используется для вставки, обновления или удаления строк в целевой таблице в зависимости от различий по сравнению с исходными данными. Представьте небольшую компанию с пятью отделами, в каждом из которых есть свой менеджер отдела. Компания решает провести реорганизацию своих отделов. Для реализации результатов реорганизации в целевой таблице dbo.Departments инструкция MERGE должна реализовать следующие изменения.

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

Следующий код создает целевую таблицу dbo.Departments и заполняет ее данными о менеджерах.

USE AdventureWorks2008R2;
GO
IF OBJECT_ID (N'dbo.Departments', N'U') IS NOT NULL 
    DROP TABLE dbo.Departments;
GO
CREATE TABLE dbo.Departments (DeptID tinyint NOT NULL PRIMARY KEY, DeptName nvarchar(30), 
    Manager nvarchar(50));
GO
INSERT INTO dbo.Departments 
    VALUES (1, 'Human Resources', 'Margheim'),(2, 'Sales', 'Byham'), 
           (3, 'Finance', 'Gill'),(4, 'Purchasing', 'Barber'),
           (5, 'Manufacturing', 'Brewer');

Организационные изменения, которые будут сделаны в отделах, хранятся в исходной таблице dbo.Departments_delta. Следующий код создает и заполняет эту таблицу:

USE AdventureWorks2008R2;
GO
IF OBJECT_ID (N'dbo.Departments_delta', N'U') IS NOT NULL 
    DROP TABLE dbo.Departments_delta;
GO
CREATE TABLE dbo.Departments_delta (DeptID tinyint NOT NULL PRIMARY KEY, DeptName nvarchar(30), 
    Manager nvarchar(50));
GO
INSERT INTO dbo.Departments_delta VALUES 
    (1, 'Human Resources', 'Margheim'), (2, 'Sales', 'Erickson'),
    (3 , 'Accounting', 'Varkey'),(4, 'Purchasing', 'Barber'), 
    (6, 'Production', 'Jones'), (7, 'Customer Relations', 'Smith');
GO

Наконец, для отражения реорганизации компании в целевой таблице в следующем коде используется инструкция MERGE, которая сравнивает исходную таблицу dbo.Departments_delta с целевой таблицей dbo.Departments. Условие поиска для данного сравнения определяется в предложении ON инструкции. В зависимости от результатов сравнения выполняются следующие действия.

Любым отделам, существующим в обеих таблицах, в таблице Departments присваиваются новые имена, новые менеджеры либо и то и другое. Если изменений не было, то обновления не происходит. Это достигается с помощью предложения WHEN MATCHED THEN.
Любые отделы в таблице Departments_delta, которых не существует в таблице Departments, будут добавлены в таблицу Departments. Это достигается с помощью предложения WHEN NOT MATCHED THEN.
Любые отделы таблицы Departments, которых не существует в исходной таблице Departments_delta, будут удалены из таблицы Departments. Это достигается с помощью предложения WHEN NOT MATCHED BY SOURCE THEN.

MERGE dbo.Departments AS d
USING dbo.Departments_delta AS dd
ON (d.DeptID = dd.DeptID)
WHEN MATCHED AND d.Manager <> dd.Manager OR d.DeptName <> dd.DeptName
    THEN UPDATE SET d.Manager = dd.Manager, d.DeptName = dd.DeptName
WHEN NOT MATCHED THEN
    INSERT (DeptID, DeptName, Manager)
        VALUES (dd.DeptID, dd.DeptName, dd.Manager)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE
OUTPUT $action, 
       inserted.DeptID AS SourceDeptID, inserted.DeptName AS SourceDeptName, 
       inserted.Manager AS SourceManager, 
       deleted.DeptID AS TargetDeptID, deleted.DeptName AS TargetDeptName, 
       deleted.Manager AS TargetManager;