Оффлайн
- Регистрация
- 9 Май 2015
- Сообщения
- 1,368
- Баллы
- 155
Синхронизация данных в двух таблицах не такая уж и редкая задача. Но, в ранних версиях Microsoft SQL Server не было средств для её эффективного решения без применения репликации.
Начиная с версии 2008 SQL Server поддерживает оператор слияния (MERGE), который позволяет выполнять процесс синхронизации более гибко.
Рассмотрим простой пример.
Допустим имеется таблица со списком продуктов питания (TestTable1).
И есть ещё одна таблица с аналогичным списком, но несколько другими данными (TestTable2).
Необходимо получить общий список всех продуктов питания в первой таблице. Совпадающие записи должны игнорироваться.
Воспользуемся оператором MERGE. Данный оператор имеет следующий синтаксис:
MERGE INTO [таблица, которая синхронизируется]
USING [таблица источник данных]
ON [условие отбора]
WHEN MATCHED THEN
[действие, если запись в таблице источнике данных соответствует условию отбора]
WHEN NOT MATCHED THEN
[действие, если запись в таблице источнике данных не соответствует условию отбора];
Точка с запятой в конце запроса MERGE обязательна.
Применительно к рассматриваемому примеру MERGE запрос будет записан следующим образом:
MERGE INTO TestTable1 t1
USING (SELECT * FROM TestTable2) t2
ON t1.Gut=t2.Gut
WHEN NOT MATCHED THEN
INSERT (Gut) VALUES (t2.Gut);
Ниже показан результат его выполнения.
Таким образом в первую таблицу были добавлены только те записи, которые в ней изначально отсутствовали.
Также в процессе синхронизации возможно не только добавление, но и обновление и удаление записей (оба действия запрещены в сегменте WHEN NOT MATCHED THEN без указания таблицы для сравнения).
Теперь удалим из первой таблицы записи, которых нет во второй. Только вместо продукта питания для разнообразия используем id.
MERGE INTO TestTable1 t1
USING (SELECT * FROM TestTable2) t2
ON t1.id<>t2.id
WHEN NOT MATCHED THEN
INSERT (Gut) VALUES (t2.Gut);
Результат его выполнения:
Для обновления создадим третью таблицу и попытаемся обновить записи во второй таблице. Условие синхронизации – совпадение id.
Запрос в данной ситуации будет выглядеть так:
MERGE INTO [dbo].[TestTable2] t2
USING (SELECT * FROM [dbo].[TestTable3]) t3
ON t2.id=t3.id
WHEN MATCHED THEN
UPDATE SET t2.Gut=t3.Gut;
Результат его выполнения:
Видно, что первые три записи второй таблицы были замещены записями из третьей.
Важно отметить, что оператор MERGE позволяет выполнять отбор и синхронизацию по нескольким полям, а также ветвления, когда в одном запросе при соответствии условию отбора выполняется одно действие, а при несоответствии другое.
Напишем MERGE запрос, который будет замещать записи из таблицы 2 записями из таблицы 3, которые с ними полностью совпадают. В противном случае запрос запишет в поле Gut таблицы 2 фразу «’Отсутствует в таблице 3».
MERGE INTO [dbo].[TestTable2] t2
USING (SELECT * FROM [dbo].[TestTable3]) t3
ON ((t2.id=t3.id) AND (t2.Gut=t3.Gut))
WHEN MATCHED THEN
UPDATE SET t2.Gut=t3.Gut
WHEN NOT MATCHED BY SOURCE THEN
UPDATE SET t2.Gut='Отсутствует в таблице 3';
Результат выполнения:
Как уже было отмечено выше, при обновлении или удалении записей в сегменте WHEN NOT MATCHED THEN необходимо указывать таблицу для сравнения. Такой таблицей может выступить как таблица источник данных, так и таблица, которая синхронизируется. Но, в обоих случаях таблицы указываются при помощи ключевых слов SOURCE или TARGET соответственно.
Пример для случая с TARGET приводить не станем, так как по своей сути он аналогичен предыдущему примеру. Только сравнение в сегменте WHEN NOT MATCHED THEN будет производиться с другой таблицей.
Также возможно использование двух сегментов WHEN NOT MATCHED THEN. Один для таблицы источника данных, а другой для таблицы, которая синхронизируется.
В данной статье мы рассмотрели лишь основы синхронизации между таблицами в SQL Server при помощи оператора MERGE.
Более полные сведения можно найти в или книгах: «SQL. Карманный справочник» (Генник Дж.. ) и «Microsoft SQL Server 2008. Разработка баз данных» (Тернстрем Т., Вебер Э., Хотек М. совместно с компанией GrandMasters).
Начиная с версии 2008 SQL Server поддерживает оператор слияния (MERGE), который позволяет выполнять процесс синхронизации более гибко.
Рассмотрим простой пример.
Допустим имеется таблица со списком продуктов питания (TestTable1).
И есть ещё одна таблица с аналогичным списком, но несколько другими данными (TestTable2).
Необходимо получить общий список всех продуктов питания в первой таблице. Совпадающие записи должны игнорироваться.
Воспользуемся оператором MERGE. Данный оператор имеет следующий синтаксис:
MERGE INTO [таблица, которая синхронизируется]
USING [таблица источник данных]
ON [условие отбора]
WHEN MATCHED THEN
[действие, если запись в таблице источнике данных соответствует условию отбора]
WHEN NOT MATCHED THEN
[действие, если запись в таблице источнике данных не соответствует условию отбора];
Точка с запятой в конце запроса MERGE обязательна.
Применительно к рассматриваемому примеру MERGE запрос будет записан следующим образом:
MERGE INTO TestTable1 t1
USING (SELECT * FROM TestTable2) t2
ON t1.Gut=t2.Gut
WHEN NOT MATCHED THEN
INSERT (Gut) VALUES (t2.Gut);
Ниже показан результат его выполнения.
Таким образом в первую таблицу были добавлены только те записи, которые в ней изначально отсутствовали.
Также в процессе синхронизации возможно не только добавление, но и обновление и удаление записей (оба действия запрещены в сегменте WHEN NOT MATCHED THEN без указания таблицы для сравнения).
Теперь удалим из первой таблицы записи, которых нет во второй. Только вместо продукта питания для разнообразия используем id.
MERGE INTO TestTable1 t1
USING (SELECT * FROM TestTable2) t2
ON t1.id<>t2.id
WHEN NOT MATCHED THEN
INSERT (Gut) VALUES (t2.Gut);
Результат его выполнения:
Для обновления создадим третью таблицу и попытаемся обновить записи во второй таблице. Условие синхронизации – совпадение id.
Запрос в данной ситуации будет выглядеть так:
MERGE INTO [dbo].[TestTable2] t2
USING (SELECT * FROM [dbo].[TestTable3]) t3
ON t2.id=t3.id
WHEN MATCHED THEN
UPDATE SET t2.Gut=t3.Gut;
Результат его выполнения:
Видно, что первые три записи второй таблицы были замещены записями из третьей.
Важно отметить, что оператор MERGE позволяет выполнять отбор и синхронизацию по нескольким полям, а также ветвления, когда в одном запросе при соответствии условию отбора выполняется одно действие, а при несоответствии другое.
Напишем MERGE запрос, который будет замещать записи из таблицы 2 записями из таблицы 3, которые с ними полностью совпадают. В противном случае запрос запишет в поле Gut таблицы 2 фразу «’Отсутствует в таблице 3».
MERGE INTO [dbo].[TestTable2] t2
USING (SELECT * FROM [dbo].[TestTable3]) t3
ON ((t2.id=t3.id) AND (t2.Gut=t3.Gut))
WHEN MATCHED THEN
UPDATE SET t2.Gut=t3.Gut
WHEN NOT MATCHED BY SOURCE THEN
UPDATE SET t2.Gut='Отсутствует в таблице 3';
Результат выполнения:
Как уже было отмечено выше, при обновлении или удалении записей в сегменте WHEN NOT MATCHED THEN необходимо указывать таблицу для сравнения. Такой таблицей может выступить как таблица источник данных, так и таблица, которая синхронизируется. Но, в обоих случаях таблицы указываются при помощи ключевых слов SOURCE или TARGET соответственно.
Пример для случая с TARGET приводить не станем, так как по своей сути он аналогичен предыдущему примеру. Только сравнение в сегменте WHEN NOT MATCHED THEN будет производиться с другой таблицей.
Также возможно использование двух сегментов WHEN NOT MATCHED THEN. Один для таблицы источника данных, а другой для таблицы, которая синхронизируется.
В данной статье мы рассмотрели лишь основы синхронизации между таблицами в SQL Server при помощи оператора MERGE.
Более полные сведения можно найти в или книгах: «SQL. Карманный справочник» (Генник Дж.. ) и «Microsoft SQL Server 2008. Разработка баз данных» (Тернстрем Т., Вебер Э., Хотек М. совместно с компанией GrandMasters).