Данная конструкция должна оканчиваться «;».

После выполнения запроса сравните 2 таблицы, их данные должны быть одинаковыми.

Конструкция MERGE чем-то напоминает условный оператор CASE, она так же содержит блоки WHEN, при выполнении условий которых происходит то или иное действие, в данном случае удаление (DELETE), обновление (UPDATE) или добавление (INSERT). Модификация данных производится в таблице приемнике.

В качестве источника может выступать запрос. Например, синхронизируем только данные по отделу 3 и для примера исключаем блок «NOT MATCHED BY SOURCE», чтобы данные не удались в случае не совпадения:

MERGE EmployeesBonusTarget trg -- таблица приемник

USING

   (

     SELECT bonus.*

     FROM EmployeesBonus bonus

     JOIN Employees emp ON bonus.EmployeeID=emp.ID

     WHERE emp.DepartmentID=3

  ) src -- источник

ON trg.EmployeeID=src.EmployeeID AND trg.BonusDate=src.BonusDate AND trg.BonusTypeID=src.BonusTypeID -- условие слияния

 

-- 2. Есть сопоставление строки trg со строкой из источника src

WHEN MATCHED THEN

UPDATE SET

trg.BonusAmount=src.BonusAmount,

trg.BonusPercent=src.BonusPercent,

trg.Note=src.Note

 

-- 3. Строка не найдена в trg, но есть в src

WHEN NOT MATCHED BY TARGET THEN -- предложение BY TARGET можно отпускать, т.е. NOT MATCHED = NOT MATCHED BY TARGET

INSERT(EmployeeID,BonusDate,BonusAmount,BonusTypeID,BonusPercent,Note)

VALUES(src.EmployeeID,src.BonusDate,src.BonusAmount,src.BonusTypeID,src.BonusPercent,src.Note);

 

Я показал работу конструкции MERGE в самом общем ее виде. При помощи нее можно реализовывать более разнообразные схемы для слияния данных, например, можно включать в блоки WHEN дополнительные условия (WHEN MATCHED AND … THEN). Это очень мощная конструкция, позволяющая в подходящих случаях сократить объем кода и совместить в рамках одного оператора функционал всех трех операторов – INSERT, UPDATE и DELETE.

И естественно с конструкцией MERGE так же можно применять CTE-выражения:

WITH cteBonus AS(

SELECT bonus.*

FROM EmployeesBonus bonus

JOIN Employees emp ON bonus.EmployeeID=emp.ID

WHERE emp.DepartmentID=3

)

MERGE EmployeesBonusTarget trg -- таблица приемник

USING cteBonus src -- источник

ON trg.EmployeeID=src.EmployeeID AND trg.BonusDate=src.BonusDate AND trg.BonusTypeID=src.BonusTypeID -- условие слияния

 

-- 2. Есть сопоставление строки trg со строкой из источника src

WHEN MATCHED THEN

UPDATE SET

trg.BonusAmount=src.BonusAmount,

trg.BonusPercent=src.BonusPercent,

trg.Note=src.Note

 

-- 3. Строка не найдена в trg, но есть в src

WHEN NOT MATCHED BY TARGET THEN -- предложение BY TARGET можно отпускать, т.е. NOT MATCHED = NOT MATCHED BY TARGET

INSERT(EmployeeID,BonusDate,BonusAmount,BonusTypeID,BonusPercent,Note)

VALUES(src.EmployeeID,src.BonusDate,src.BonusAmount,src.BonusTypeID,src.BonusPercent,src.Note);

 

В общем, я постарался вам задать направление, более подробнее, в случае необходимости, изучайте уже самостоятельно.

Использование конструкции OUTPUT

Конструкция OUTPUT дает возможность получить информацию по строкам, которые были добавлены, удалены или изменены в результате выполнения DML команд INSERT, DELETE, UPDATE и MERGE. Данная конструкция, представляет расширение для операций модификации данных и в каждой СУБД может быть реализовано по-своему, либо вообще отсутствовать.

Конструкция OUTPUT имеет 2 основные формы:

1. OUTPUT перечень_выражений – используется для возврата результата в виде набора

2. OUTPUT перечень_выражений INTO принимающая_таблица(список_полей) – используется для вставки результата в указанную таблицу

 

Рассмотрим первую форму

Добавим в таблицу Positions новые записи:

INSERT Positions(Name)

OUTPUT inserted.*

VALUES

(N'Test 1'),

(N'Test 2'),

(N'Test 3')

 

После выполнения данной операции, записи будут вставлены в таблицу Positions и в добавок мы увидим информацию по добавленным строкам на экране.

Ключевое слово «inserted» дает нам доступ к значениям добавленных строк. В данном случае использование «inserted.*» вернет нам информацию по всем полям, которые есть в таблице Positions (ID и Name).

Так же после OUTPUT вы можете явно указать возвращаемый на экран перечень полей посредством «inserted.имя_поля», также вы можете использовать разные выражения:

INSERT Positions(Name)

OUTPUT inserted.ID,inserted.Name,'I'

VALUES

(N'Test 4'),

(N'Test 5'),

(N'Test 6')

 

При использовании DML команды DELETE, доступ к значениям измененных строк получается при помощи ключевого слова «deleted»:

DELETE Positions

OUTPUT deleted.ID,deleted.Name,'D'

WHERE Name LIKE N'Test%'

 

При использовании DML команды UPDATE, мы можем использовать ключевое слово:

·deleted – для того, чтобы получить доступ к значениям строки, которые были до обновления (старые значения)

·inserted – для того, чтобы получить новые значения строки

 

Продемонстрируем на таблице Employees:

UPDATE Employees

SET

LastName=N'Александров',

FirstName=N'Александр'

OUTPUT

deleted.ID,

deleted.LastName [Старая Фамилия],

deleted.FirstName [Старое Имя],

inserted.ID,

inserted.LastName [Новая Фамилия],

inserted.FirstName [Новое Имя]

WHERE ID=1005

 

ID	Старая Фамилия	Старое Имя	ID	Новая Фамилия	Новое Имя
1005	NULL	NULL	1005	Александров	Александр

 

В случае MERGE мы можем так же использовать «inserted» и «deleted» для доступа к значениям обработанных строк.

Давайте для примера создадим таблицу PositionsTarget, на которой после будет показан пример с MERGE:

SELECT

CAST(ID AS int) ID, -- чтобы поле создалось без опции IDENTITY

Name+'-old' Name -- изменим название

INTO PositionsTarget

FROM Positions

WHERE ID=2 -- вставим только одну должность

 

Добавим в PositionsTarget мусора:

INSERT PositionsTarget(ID,Name)VALUES

(100,N'Qwert'),

(101,N'Asdf')

 

Выполним команду MERGE с конструкцией OUTPUT:

MERGE PositionsTarget trg -- таблица приемник

USING Positions src -- таблица источник

ON trg.ID=src.ID -- условие слияния

 

-- 1. Строка есть в trg но нет сопоставления со строкой из src

WHEN NOT MATCHED BY SOURCE THEN

DELETE

 

-- 2. Есть сопоставление строки trg со строкой из источника src

WHEN MATCHED THEN

UPDATE SET

trg.Name=src.Name

 

-- 3. Строка не найдена в trg, но есть в src

WHEN NOT MATCHED BY TARGET THEN -- предложение BY TARGET можно отпускать, т.е. NOT MATCHED = NOT MATCHED BY TARGET

INSERT(ID,Name)

VALUES(src.ID,src.Name)

 

OUTPUT

deleted.ID Old_ID,

deleted.Name Old_Name,

inserted.ID New_ID,

inserted.Name New_Name,

CASE

WHEN deleted.ID IS NOT NULL AND inserted.ID IS NOT NULL THEN 'U'

WHEN deleted.ID IS NOT NULL THEN 'D'

WHEN inserted.ID IS NOT NULL THEN 'I'

END OperType;

 

Old_ID	Old_Name	New_ID	New_Name	OperType
NULL	NULL	1	Бухгалтер	I
2	Директор-old	2	Директор	U
NULL	NULL	3	Программист	I
NULL	NULL	4	Старший программист	I
100	Qwert	NULL	NULL	D
101	Asdf	NULL	NULL	D

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

Рассмотрим вторую форму

У конструкции OUTPUT, есть и более важное предназначение – она позволяет не только получить, но и зафиксировать (OUTPUT … INTO …) информацию о том, что уже произошло по факту, то есть после выполнения операции модификации. Она может оказаться полезна в случае логированния произошедших действий. В некоторых случаях, ее можно использовать как хорошую альтернативу тригерам (для прозрачности действий).

Давайте создадим демонстрационную таблицу, для логирования изменений по таблице Positions:

CREATE TABLE PositionsLog(

LogID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_PositionsLog PRIMARY KEY,

ID int,

Old_Name nvarchar(30),

New_Name nvarchar(30),

LogType char(1) NOT NULL,

LogDateTime datetime NOT NULL DEFAULT SYSDATETIME()

)

 

А теперь сделаем при помощи конструкции (OUTPUT … INTO …) запись в эту таблицу:

-- добавление

INSERT Positions(Name)

OUTPUT inserted.ID,inserted.Name,'I' INTO PositionsLog(ID,New_Name,LogType)

VALUES

(N'Test 1'),

(N'Test 2')

 

-- обновление

UPDATE Positions

SET

Name+=' - new' -- обратите внимание на синтаксис "+=", аналогично Name=Name+' - new'

OUTPUT

deleted.ID,

deleted.Name,

inserted.Name,

'U'

INTO PositionsLog(ID,Old_Name,New_Name,LogType)

WHERE Name LIKE N'Test%'

 

-- удаление

DELETE Positions

OUTPUT deleted.ID,deleted.Name,'D' INTO PositionsLog(ID,Old_Name,LogType)

WHERE Name LIKE N'Test%'

 

Посмотрите, что получилось:

SELECT * FROM PositionsLog