Предложение MERGE в пакетах служб Integration Services

  • Статья

Применимо к: SQL Server Integration Runtime служб SSIS в Фабрика данных Azure

В текущем выпуске служб SQL Server Integration Services инструкция SQL в задаче «Выполнение SQL» может содержать инструкцию MERGE. Эта инструкция MERGE позволяет выполнять несколько операций INSERT, UPDATE и DELETE в единой инструкции.

Для использования в пакете инструкции MERGE выполните следующее.

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

  • Создайте задачу «Выполнение SQL», содержащую инструкцию MERGE.

  • Соедините задачу потока данных с задачей «Выполнение SQL» и используйте данные из промежуточной таблицы в качестве входных данных инструкции MERGE.

    Примечание

    Хотя инструкции MERGE в этом случае обычно требуется промежуточная таблица, все равно эта инструкция обычно выполняется быстрее, чем уточняющие запросы для всех строк, выполняемые преобразованием «Уточняющий запрос». Инструкция MERGE полезна также при большом размере таблицы уточняющих запросов. В этом случае преобразованию «Уточняющий запрос» может не хватить памяти для кэширования ссылочной таблицы.

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

Обычно инструкция MERGE используется для применения изменений, в том числе операций вставки, редактирования и удаления, выполненных в одной таблице, к другой таблице. До SQL Server 2008 (10.0.x) этот процесс требовал преобразования Уточняющий запрос и несколько преобразований команд OLE DB. Преобразование «Уточняющий запрос» выполняло уточняющие запросы к каждой строке, чтобы определить, какие из них были добавлены или изменены. Затем преобразования «Команда OLE DB» выполняли необходимые операции INSERT, UPDATE и DELETE. Начиная с SQL Server 2008 г. (10.0.x) одна инструкция MERGE может заменить как преобразование Уточняющий запрос, так и соответствующие преобразования команды OLE DB.

Инструкция MERGE с добавочной загрузкой

Новая функция отслеживания измененных данных в SQL Server 2008 (10.0.x) упрощает надежное выполнение добавочных загрузок в хранилище данных. В качестве альтернативы параметризованным преобразованиям «Команда OLE DB», с помощью которых производились вставки и обновления, можно использовать инструкцию MERGE для объединения этих операций.

Дополнительные сведения см. в разделе Применение изменений в назначении.

Инструкция MERGE в других сценариях

В следующих сценариях инструкцию MERGE можно использовать как внутри, так и вне пакета служб Службы Integration Services . Однако пакетам служб Службы Integration Services часто приходится загружать эти данные из нескольких разнородных источников, а затем объединять их и проводить очистку. Вместо этого можно использовать в пакете инструкцию MERGE. Это удобно и упрощает поддержку.

Отслеживание привычек покупателей

Предположим, что в хранилище данных существует таблица FactBuyingHabits, в которой отслеживаются последние даты покупки каждым клиентом определенного товара. Таблица состоит из столбцов ProductID, CustomerID и PurchaseDate. Каждую неделю транзакционная база данных создает таблицу PurchaseRecords, куда входят покупки этой недели. Цель — применить единственную инструкцию MERGE для добавления данных из таблицы PurchaseRecords в таблицу FactBuyingHabits. Для пар товар-заказчик, которые не существовали, инструкция MERGE добавляет новые строки. Для существующих пар товар-заказчик инструкция MERGE изменяет дату последней покупки.

Отслеживание журнала цен

Таблица DimBook представляет собой список книг на складе продавца книг. В ней содержится журнал цен на каждую книгу. Эта таблица содержит следующие столбцы: ISBN, ProductID, Price, Shelf и IsCurrent. Таблица содержит по одной строке на каждую цену, которая когда-либо была у книги. Одна из строк содержит текущую цену. Чтобы указать, какая именно строка содержит текущую цену, столбец IsCurrent этой строки содержит значение 1.

Еженедельно база данных создает таблицу WeeklyChanges, в которой содержатся изменения цен за неделю и новые книги, добавленные за эту неделю. С помощью единственной инструкции MERGE можно перенести изменения таблицы WeeklyChanges в таблицу DimBook. Инструкция MERGE добавляет новые строки для вновь добавленных книг и изменяет столбец IsCurrent на 0 для строк существующих книг, у которых изменились цены. Инструкция MERGE также добавляет новые строки для книг, у которых изменились цены, и устанавливает для столбца IsCurrent этих строк значение 1.

Слияние таблицы новых данных со старой таблицей

База данных моделирует свойства объекта, используя "открытую схему". Это значит, что таблица содержит пары имя-значение для каждого свойства. У таблицы Properties три столбца: EntityID, PropertyID и Value. Таблица NewProperties представляет собой обновленную версию таблицы, которую следует синхронизировать с таблицей Properties. Для синхронизации этих двух таблиц можно с помощью инструкции MERGE выполнить следующие операции:

  • удалить свойства из таблицы Properties, если их нет в таблице NewProperties;

  • изменить значения свойств из таблицы Properties на новые, найденные в таблице NewProperties;

  • создать новые свойства, присутствующие в таблице NewProperties, если их нет в таблице Properties.

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

Ведение инвентарного учета

База данных Inventory содержит таблицу ProductsInventory, в которой есть столбцы ProductID и StockOnHand. Таблица Shipments со столбцами ProductID, CustomerID и Quantity хранит данные об отгрузке товаров заказчикам. Таблица ProductInventory должна ежедневно обновляться на основании данных из таблицы Shipments. Уменьшить указанное в таблице ProductInventory количество товара на основании данных об отгрузке можно с помощью единственной инструкции MERGE. Если запас товара на складе упал до 0, инструкция MERGE может также удалить строку этого продукта из таблицы ProductInventory.