АссистентНовый вопросИсторияОбласти знаний
Партнёрские проектыСпецпроектыСтать автором
Сменить язык
О РУВИКИ

Merge (SQL)

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

Оператор был официально представлен в стандарте SQL: 2003 и расширен в стандарте SQL: 2008.

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

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

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

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

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

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

Синтаксис

-- 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[3].

Аргументы

WITH

Указывает временный именованный результирующий набор или представление (которые также называются обобщённым табличным выражением), определённые в области инструкции 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

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

[ AS ] table_alias

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

ON

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

WHEN MATCHED THEN

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

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

WHEN NOT MATCHED [ BY TARGET ] THEN

Указывает, что в таблицу 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

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

AND

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

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

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

INDEX (index_val [ ,…n ])

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

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

OPTION ( [ ,…n ])

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

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

UPDATE SET

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

DELETE

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

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

(column_list)

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

VALUES (values_list)

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

DEFAULT VALUES

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

Задаёт условия поиска для указания <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.

Примечания

  1. ↑ SQL Server Merge to insert, update and delete at the same time (англ.). www.mssqltips.com. Дата обращения: 22 сентября 2022.
  2. ↑ 1 2 Aveek Das. Understanding the SQL Merge statement (амер. англ.). SQL Shack - articles about database auditing, server performance, data recovery, and more (27 июля 2020). Дата обращения: 22 сентября 2022.
  3. ↑ 1 2 mstehrani. Merge (Transact-SQL) - SQL Server (амер. англ.). learn.microsoft.com. Дата обращения: 22 сентября 2022.

Ссылки

Дополнительно по теме

Категории