Merge (SQL)
Merge — оператор языка SQL, который позволяет слить данные одной таблицы с данными другой таблицы. При слиянии таблиц проверяется условие, и если оно истинно, то выполняется Update, а если нет — Insert. Причём нельзя изменять поля таблицы в секции Update, по которым идет связывание двух таблиц. Данные изменяются или добавляются только для таблицы в предложении MERGE INTO, таблица в предложении USING остается без изменений.
Оператор был официально представлен в стандарте SQL:2003 и расширен в стандарте SQL: 2008[источник не указан 517 дней].
Применение оператора 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 <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.
Примечания
- ↑ SQL Server MERGE to insert, update and delete at the same time (англ.). www.mssqltips.com. Дата обращения: 22 сентября 2022. Архивировано 22 сентября 2022 года.
- ↑ 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. Архивировано 22 сентября 2022 года.
- ↑ 1 2 mstehrani. MERGE (Transact-SQL) - SQL Server (амер. англ.). learn.microsoft.com. Дата обращения: 22 сентября 2022.
Ссылки
Источник: https://web.archive.org/web/20111120170710/http://oracle-wiki.ru/wiki/Merge