Merge — оператор языка SQL , который позволяет слить данные одной таблицы с данными другой таблицы. При слиянии таблиц проверяется условие, и если оно истинно, то выполняется Update , а если нет — Insert . Причём нельзя изменять поля таблицы в секции Update, по которым идет связывание двух таблиц. Данные изменяются или добавляются только для таблицы в предложении MERGE INTO, таблица в предложении USING остается без изменений.

Оператор был официально представлен в стандарте SQL:2003 и расширен в стандарте SQL: 2008 ^{[ источник не указан 182 дня ]} .

Применение оператора SQL MERGE

В типичном решении для хранилища данных SQL часто важно поддерживать историю данных в хранилище со ссылкой на исходные данные, которые передаются инструменту ETL . Наиболее распространенный вариант использования — попытка поддерживать медленно меняющиеся измерения (SCD) в хранилище данных. В таких случаях необходимо вставить новые записи в хранилище данных, удалить или пометить записи из хранилища, которых больше нет в источнике, и обновить данные в хранилище, которые были обновлены в источнике .

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

Оптимизация производительности оператора SQL MERGE

Есть несколько аспектов, с помощью которых возможно оптимизировать производительность операторов MERGE. Появилась возможность записи операторов DML (INSERT, UPDATE и DELETE), объединенных в один оператор. С точки зрения обработки данных это полезно, так как сокращает операции ввода-вывода с диска для каждого из трех операторов в отдельности, и даёт возможность данным считываться только один раз .

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

Синтаксис

-- SQL Server and Azure SQL Database
[ WITH <common_table_expression> [,...n] ]  
MERGE
    [ TOP ( expression ) [ PERCENT ] ]
    [ INTO ] <target_table> [ WITH ( <merge_hint> ) ] [ [ AS ] table_alias ]  
    USING <table_source> [ [ AS ] table_alias ]
    ON <merge_search_condition>  
    [ WHEN MATCHED [ AND <clause_search_condition> ]  
        THEN <merge_matched> ] [ ...n ]  
    [ WHEN NOT MATCHED [ BY TARGET ] [ AND <clause_search_condition> ]  
        THEN <merge_not_matched> ]  
    [ WHEN NOT MATCHED BY SOURCE [ AND <clause_search_condition> ]  
        THEN <merge_matched> ] [ ...n ]  
    [ <output_clause> ]  
    [ OPTION ( <query_hint> [ ,...n ] ) ]
;  

<target_table> ::=  
{
    [ database_name . schema_name . | schema_name . ]  
  target_table  
}  

<merge_hint>::=  
{  
    { [ <table_hint_limited> [ ,...n ] ]  
    [ [ , ] INDEX ( index_val [ ,...n ] ) ] }  
}  

<merge_search_condition> ::=  
    <search_condition>  

<merge_matched>::=  
    { UPDATE SET <set_clause> | DELETE }  

<merge_not_matched>::=  
{  
    INSERT [ ( column_list ) ]
        { VALUES ( values_list )  
        | DEFAULT VALUES }  
}  

<clause_search_condition> ::=  
    <search_condition>

при этом:

• каждый оператор MERGE должен заканчиваться точкой с запятой. Если в конце оператора MERGE нет точки с запятой, будет выдана ошибка;
• можно использовать SELECT @@RowCount после написания оператора MERGE, который вернет количество записей, измененных транзакцией;
• для работы оператора MERGE обязательно наличие одного из предложений MATCHED .

Аргументы

WITH <common_table_expression>

Указывает временный именованный результирующий набор или представление (которые также называются обобщенным табличным выражением), определенные в области инструкции MERGE. Результирующий набор, на который ссылается инструкция MERGE, является производным простого запроса.

TOP ( expression ) [ PERCENT ]

Указывает количество или процент затронутых строк. expression может быть либо числом, либо процентом от числа строк. Строки, на которые ссылается выражение TOP, не расположены в определенном порядке.

database_name

Имя базы данных, в которой расположена таблица target_table .

schema_name

Имя схемы, к которой принадлежит таблица target_table .

target_table

Таблица или представление, с которыми выполняется сопоставление строк данных из таблицы <table_source> по условию <clause_search_condition>. Таблица target_table является целевым объектом любых операций вставки, обновления или удаления, указанных предложениями WHEN в инструкции MERGE. target_table не может быть удаленно расположенной таблицей. Для таблицы target_table не должно существовать определенных правил.

Указания можно задать как <merge_hint>.

[ AS ] table_alias

Альтернативное имя для ссылок на таблицу для target_table .

USING <table_source>

Указывает источник данных, который сопоставляется со строками данных в таблице target_table на основе условия <merge_search condition>. Результат этого совпадения обуславливает действия, которые выполняются предложениями WHEN инструкции MERGE. Аргумент <table_source> может быть удаленной таблицей или производной таблицей, которая обращается к удаленным таблицам.

[ AS ] table_alias

Альтернативное имя для ссылок на таблицу для table_source.

ON <merge_search_condition>

Указывает условия, по которым <table_source> соединяется с таблицей target_table для сопоставления. Необходимо указать столбцы целевой таблицы, которые сравниваются с соответствующим столбцом исходной таблицы.

WHEN MATCHED THEN <merge_matched>

Указывает, что все строки *target_table, которые соответствуют строкам, возвращенным выражением <table_source> ON <merge_search_condition>, и удовлетворяют дополнительным условиям поиска, обновляются или удаляются в соответствии с предложением <merge_matched>.

Инструкция MERGE включать не больше двух предложений WHEN MATCHED. Если указаны два предложения, первое предложение должно сопровождаться предложением AND <search_condition>.

WHEN NOT MATCHED [ BY TARGET ] THEN <merge_not_matched>

Указывает, что в таблицу target_table вставляется строка для каждой строки, возвращенной выражением <table_source> ON <merge_search_condition>, которая не соответствует строке в таблице target_table , но удовлетворяет дополнительному условию поиска (если оно есть). Значения для вставки указываются с помощью предложения <merge_not_matched>. Инструкция MERGE может иметь только одно предложение WHEN NOT MATCHED [ BY TARGET ].

WHEN NOT MATCHED BY SOURCE THEN <merge_matched>

Указывает, что все строки *target_table, которые не соответствуют строкам, возвращенным выражением <table_source> ON <merge_search_condition>, и удовлетворяют дополнительным условиям поиска, обновляются или удаляются в соответствии с предложением <merge_matched>.

AND <clause_search_condition>

Указывается любое действительное условие поиска.

<table_hint_limited>

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

Использование ключевых слов NOLOCK и READUNCOMMITTED запрещено.

INDEX (index_val [ ,…n ])

Указывает имя или идентификатор одного или нескольких индексов целевой таблицы для выполнения неявного соединения с исходной таблицей.

<output_clause>

Возвращает по одной строке для каждой строки в таблице target_table , в которой выполнена операция обновления, вставки или удаления, без какого-либо определенного порядка. Параметр $action может быть указан в предложении вывода. $action — это столбец типа nvarchar(10), который возвращает одно из трех значений для каждой строки: INSERT, UPDATE или DELETE — согласно действию, которое было выполнено с этой строкой. Предложение OUTPUT рекомендуется использовать для запроса или подсчета строк, на которые влияет предложение MERGE.

OPTION (<query_hint> [ ,…n ])

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

<merge_matched>

Указывает действие обновления или удаления, применяемое ко всем строкам таблицы target_table , которые не соответствуют строкам, возвращенным выражением <table_source> ON <merge_search_condition>, и удовлетворяют дополнительным условиям поиска.

UPDATE SET <set_clause>

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

DELETE

Указывает, что строки, совпадающие со строками в target_table , удаляются.

<merge_not_matched>

Указываются значения для вставки в целевую таблицу.

( column_list )

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

VALUES ( values_list )

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

DEFAULT VALUES

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

<search_condition>

Задает условия поиска для указания <merge_search_condition> или <clause_search_condition>.

Определяет шаблон сопоставления графов.

Remarks

Должно быть указано по крайней мере одно из трех предложений MATCHED, но они могут быть указаны в любом порядке. В одном предложении MATCHED переменная не может быть обновлена больше одного раза.

На все операции удаления, вставки или обновления, применяемые инструкцией MERGE к целевой таблице, распространяются все ограничения, определенные для этой таблицы, включая все каскадные ограничения целостности данных. Если IGNORE_DUP_KEY имеет значение ON для любого из уникальных индексов целевой таблицы, то инструкция MERGE игнорирует этот параметр.

Чтобы использовать инструкцию MERGE, необходима точка с запятой (;) как признак конца инструкции. Возникает ошибка 10713, если инструкция MERGE выполняется без признака конца конструкции.

Пример

 MERGE INTO table_name USING table_reference ON (condition)
   WHEN MATCHED THEN
   UPDATE SET column1 = value1 [, column2 = value2 …]
   WHEN NOT MATCHED THEN
   INSERT (column1 [, column2 …]) VALUES (value1 [, value2 …]);

Реализации

Данный оператор реализован в следующих системах управления базами данных Oracle Database, IBM Db2, Teradata, EXASOL, Firebird, CUBRID, H2, HSQLDB, MS SQL, Vectorwise, Apache Derby и PostgreSQL (с 15ой версии).

Также оператор используется в базе данных Microsoft Azure SQL.

Примечания

Ссылки

Источник: