Как работает merge sql

SQL-Ex blog

Merge Joins (соединения слиянием) теоретически являются самыми быстрыми физическими операторами соединения, однако они требуют, чтобы данные обоих входов были отсортированы.

Базовый алгоритм работает следующим образом: SQL Server сравнивает первые строки обоих отсортированных входов. Затем сравнение продолжается со следующими строками второго входа до тех пор, пока значения соответствуют значению первого входа.

Если соответствий больше нет, SQL Server переходит к следующей строке того входа, который имеет меньшее значение — и затем продолжает выполнение сравнений, выводя каждую соединенную запись. (Подробней об операции merge join можно почитать в публикации Крейга Фридмана).

Это эффективно, поскольку в большинстве случаев SQL Server не придется возвращаться и читать неоднократно одни и те же строки. Исключение имеет место, когда существуют дубликаты сравниваемых значений в обеих входных таблицах (или, точнее, когда SQL Server не имеет метаданных, которые бы сообщали, что в обеих таблицах отсутствуют дубликаты) и SQL Server вынужден выполнять соединение слиянием типа многие-ко-многим.

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

Что показывают нам соединения слиянием?

Знание механизма выполнения merge join позволяет нам понять, что думает оптимизатор о наших данных и восходящих потоках данных от операторов соединения. Это дает нам возможность сосредоточиться на настройке производительности.

• Оптимизатор выбирает использование merge join, когда входные данные уже отсортированы или SQL Server может выполнить сортировку данных с относительно небольшой стоимостью. Кроме того, оптимизатор весьма пессимистичен относительно вычисления стоимости merge joins, поэтому, если merge join попадает в ваши планы, это, скорее всего, говорит об их эффективности.
• Хотя merge join может быть эффективен, всегда полезно посмотреть, почему данные, поступающие в этот оператор, уже отсортированы:
  • Если сортировка возникла благодаря тому, что merge join вытащил данные непосредственно из индекса, отсортированного по ключу соединения, то тут не о чем беспокоиться.
  • Если же оптимизатор добавил сортировку в восходящий поток данных для merge join, то полезно выяснить, не заставит ли эта предварительная сортировка SQL Server выполнять лишние сортировки . Зачастую достаточно просто переопределить включенный в индекс столбец на ключевой столбец — если вы добавляете его последним столбцом в ключ индекса, то отрицательное влияние подобного действия минимально, зато вы позволите SQL Server использовать merge join без всякой дополнительной сортировки.

  ЗАМЕЧАНИЕ. Всегда имеются исключения из правил. Merge join имеет самый быстрый алгоритм, поскольку каждую строку из входных источников данных требуется прочитать только один раз. Однако возможности оптимизации, имеющие место в других операторах соединения, могут при определенных обстоятельствах дать лучшую производительность.

  Например, внешняя таблица с единственной строкой и индексированной внутренней таблице при использовании nested loops join (соединение вложенными циклами) превзойдет merge join при тех же условиях за счет оптимизации внутренних циклов:

  DROP TABLE IF EXISTS T1; 
  GO 
  CREATE TABLE T1 (Id int identity PRIMARY KEY, Col1 CHAR(1000)); 
  GO 
  INSERT INTO T1 VALUES(''); 
  GO 
  DROP TABLE IF EXISTS T2; 
  GO 
  CREATE TABLE T2 (Id int identity PRIMARY KEY, Col1 CHAR(1000)); 
  GO 
  INSERT INTO T2 VALUES(''); 
  GO 100 
  -- Включите планы выполнения и проверьте фактическое число строк для T2 
  SELECT * 
  FROM T1 INNER LOOP JOIN T2 ON T1.Id = T2.Id; 
  SELECT * 
  FROM T1 INNER MERGE JOIN T2 ON T1.Id = T2.Id;

  Также могут быть ситуации, когда входы с множеством дубликатов записей, требующие использования рабочей таблицы, могут оказаться медленней, чем nested loop join.

  Хотя, как упомянуто ранее, я нахожу подобные сценарии больше исключениями из правил использования merge join в реальном мире.

  Оператор MERGE

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

  Эта задача подразумевает выполнение двух разных операторов (INSERT и UPDATE) на одной таблице (Ships) в зависимости от наличия/отсутствия связанных записей в другой таблице (Outcomes).

  Для решения подобных задач стандарт предоставляет оператор MERGE . Рассмотрим его использование на примере решения данной задачи в SQL Server.

  Для начала напишем запрос, который вернет нам головные корабли из таблицы Outcomes, т.е. корабли, у которых имя класса совпадает с именем корабля:

  Консоль

  Выполнить

  Теперь добавим соединение с таблицей Battles и выполним группировку, чтобы найти минимальный год сражений каждого такого корабля:

  Консоль

  Выполнить

  Исходные данные готовы. Теперь мы можем перейти к написанию оператора MERGE.

  Предложение OUTPUT позволяет вывести измененные строки. Автоматически создаваемые рабочие таблицы inserted и deleted имеют тот же смысл, что и при использовании в триггерах, т.е. inserted содержит строки, которые были добавлены в изменяемую таблицу, а deleted — удаленные из нее строки.

  Поскольку удаления в нашем запросе не было, то соответствующие столбцы имеют значения NULL. Столбец $action содержит название выполненной операции. В нашем случае была выполнена только вставка, поскольку корабль Tennessee содержится в таблице Ships с известным годом спуска на воду:

  Консоль

  Выполнить

  Инструкция MERGE может иметь не больше двух предложений WHEN MATCHED .

  Если указаны два предложения, то первое предложение должно сопровождаться дополнительным условием (что имеет место в нашем случае — AND target.launched IS NULL). Для любой строки второе предложение WHEN MATCHED применяется только в том случае, если не применяется первое.

  Если имеются два предложения WHEN MATCHED, одно должно указывать действие UPDATE, а другое — DELETE. Т.е. если мы добавим в оператор предложение

  то удалим корабль Tennessee:

  Инструкцию MERGE нельзя использовать для обновления одной строки более одного раза, а также для обновления и удаления одной и той же строки.

  Предложение WHEN NOT MATCHED [BY TARGET] THEN INSERT используется для вставки строк из источника, не совпадающих со строками в изменяемой таблице согласно условию связи. В нашем примере такой строкой является строка, относящаяся к кораблю Bismarck. Инструкция MERGE может иметь только одно предложение WHEN NOT MATCHED.

  Наконец, оператор MERGE может включать предложение WHEN NOT MATCHED BY SOURCE THEN.

  Оно воздействует на те строки изменяемой таблицы, для которых нет соответствия в таблице-источнике. Например, если бы мы хотели удалить из таблицы Ships головные корабли, не принимавшие участие в сражениях, то добавили бы следующее предложение:

  При помощи этого предложения можно удалять или обновлять строки. Инструкция MERGE может иметь не более двух предложений WHEN NOT MATCHED BY SOURCE. Если указаны два предложения, то первое предложение должно иметь дополнительное условие (как в нашем примере). Для любой выбранной строки второе предложение WHEN NOT MATCHED BY SOURCE применяется только в тех случаях, если не применяется первое. Кроме того, если имеется два предложения WHEN NOT MATCHED BY SOURCE, то одно должно выполнять UPDATE, а другое — DELETE.

  Как работает merge sql

  MERGE — добавить, изменить или удалить строки таблицы по условию

  Синтаксис

  [ WITH запрос_WITH [, . ] ] MERGE INTO [ ONLY ] имя_целевой_таблицы [ * ] [ [ AS ] целевой_псевдоним ] USING источник_данных ON условие_соединения предложение_when [. ] здесь источник_данных: < [ ONLY ] имя_исходной_таблицы [ * ] | ( исходный_запрос ) > [ [ AS ] исходный_псевдоним ] и предложение_when: < WHEN MATCHED [ AND условие ] THEN < изменение_при_объединении | удаление_при_объединении | DO NOTHING > | WHEN NOT MATCHED [ AND условие ] THEN < добавление_при_объединении | DO NOTHING > > и добавление_при_объединении: INSERT [( имя_столбца [, . ] )] [ OVERRIDING < SYSTEM | USER >VALUE ] < VALUES ( < выражение | DEFAULT > [, . ] ) | DEFAULT VALUES > и изменение_при_объединении: UPDATE SET < имя_столбца = < выражение | DEFAULT > | ( имя_столбца [, . ] ) = ( < выражение | DEFAULT > [, . ] ) > [, . ] и удаление_при_объединении: DELETE

  Описание

  Операция MERGE выполняет действия, которые меняют строки в таблице имя_целевой_таблицы , используя источник_данных . MERGE — это один SQL -оператор, который по условию выполняет со строками действия INSERT , UPDATE или DELETE ; сделать то же самое без MERGE можно, только используя несколько операторов процедурного языка.

  Сначала команда MERGE выполняет соединение источника_данных с таблицей имя_целевой_таблицы , формируя ноль или более строк-кандидатов на изменение. Для каждой строки-кандидата устанавливается неизменяемый позже статус MATCHED (совпадает) или NOT MATCHED (не совпадает), после чего вычисляются условия WHEN в заданном порядке. Для каждой отдельной строки будет выполняться действие первого же предложения, условие которого выдаст true. При этом для каждой строки-кандидата может быть выполнено действие не более чем одного предложения WHEN .

  Действия операции MERGE имеют тот же эффект, что и обычные одноимённые команды UPDATE , INSERT или DELETE . Синтаксис этих команд в MERGE отличается, в частности, отсутствием предложения WHERE и имени таблицы. Действия этих команд выполняются с таблицей имя_целевой_таблицы , хотя посредством триггеров могут быть изменены и другие таблицы.

  С указанием DO NOTHING исходная строка пропускается. Поскольку применимость действий оценивается в заданном порядке, используя DO NOTHING , удобно пропускать исходные строки, не представляющие интерес, чтобы затем более детально обрабатывать остальные.

  Для MERGE не предусмотрено отдельное право. Когда в этой команде указывается действие UPDATE , у вас должно быть право UPDATE для столбцов таблицы имя_целевой_таблицы , на которые ссылается предложение SET . Когда указывается действие INSERT или DELETE , у вас должно быть соответствующее право для таблицы имя_целевой_таблицы . Права проверяются один раз в начале выполнения оператора, вне зависимости от того, будут ли выполняться конкретные предложения WHEN . Кроме того, необходимо иметь право SELECT для источника_данных и столбцов таблицы имя_целевой_таблицы , которые фигурируют в условии .

  Оператор MERGE не поддерживается для отношений, являющихся материализованными представлениями, сторонними таблицами, или если для них заданы какие-либо правила.

  Параметры

  имя_целевой_таблицы

  Имя (возможно, дополненное схемой) целевой таблицы, принимающей результат объединения. Если перед именем таблицы добавлено ONLY , соответствующие строки изменяются или удаляются только в указанной таблице. Без ONLY соответствующие строки также изменяются или удаляются во всех таблицах, унаследованных от указанной таблицы. При желании, после имени таблицы можно указать * , чтобы явно обозначить, что операция затрагивает все дочерние таблицы. Ключевое слово ONLY и параметр * не влияют на действия INSERT , добавляющие строки только в указанную таблицу. целевой_псевдоним

  Альтернативное имя целевой таблицы. Когда это имя задаётся, настоящее имя таблицы полностью скрывается. Например, в запросе MERGE INTO foo AS f остальные компоненты оператора MERGE должны обращаться к целевой таблице по имени f , а не foo . имя_исходной_таблицы

  Имя (возможно, дополненное схемой) исходной таблицы, представления или переходной таблицы. Если перед именем таблицы добавлено ONLY , соответствующие строки берутся только из указанной таблицы. Без ONLY строки также берутся из всех таблиц, унаследованных от указанной. При желании, после имени таблицы можно указать * , чтобы явно обозначить, что операция затрагивает все дочерние таблицы. исходный_запрос

  Запрос (оператор SELECT или оператор VALUES ), предоставляющий строки для объединения в таблице имя_целевой_таблицы . За информацией о синтаксисе обратитесь к описанию SELECT и VALUES . исходный_псевдоним

  Альтернативное имя для источника данных. Когда задаётся этот псевдоним, он полностью скрывает настоящее имя таблицы или тот факт, что это результат запроса. условие_соединения

  Задаваемое условие_соединения представляет собой выражение, выдающее значение типа boolean (как в предложении WHERE ), которое определяет, какие строки в источнике_данных соответствуют строкам в таблице имя_целевой_таблицы .

  Предупреждение

  В условии_соединения должны фигурировать только столбцы таблицы имя_целевой_таблицы , по которым её строки сопоставляются со строками источника_данных . Подвыражения условия_соединения , ссылающиеся только на столбцы таблицы имя_целевой_таблицы , могут влиять на выполняемое действие, часто неожиданным образом.

  предложение_when

  В команде MERGE должно быть минимум одно предложение WHEN .

  Если в предложении WHEN указано WHEN MATCHED и строка-кандидат на изменение соответствует строке таблицы имя_целевой_таблицы , предложение WHEN выполняется, когда условие отсутствует или выдаёт true .

  И наоборот, если в предложении WHEN указано WHEN NOT MATCHED и строка-кандидат на изменение не соответствует строке таблицы имя_целевой_таблицы , предложение WHEN выполняется, когда условие отсутствует или выдаёт true . условие

  Выражение, выдающее значение типа boolean . Если это выражение для предложения WHEN выдаёт true , для данной строки выполняется действие этого предложения.

  Условие в предложении WHEN MATCHED может ссылаться на столбцы как исходного, так и целевого отношения. Условие в предложении WHEN NOT MATCHED может ссылаться только на столбцы исходного отношения, поскольку соответствующей целевой строки нет по определению. В целевой таблице доступны только системные атрибуты. добавление_при_объединении

  Указание действия INSERT , добавляющего одну строку в целевую таблицу. Имена целевых столбцов могут перечисляться в любом порядке. Если список имён столбцов не задан вовсе, по умолчанию используются все столбцы таблицы в порядке объявления.

  Все столбцы, не представленные в явном или неявном списке столбцов, получат значения по умолчанию, если для них заданы эти значения, либо NULL в противном случае.

  Если таблица имя_целевой_таблицы является секционированной, каждая строка направляется в соответствующую секцию и добавляется в неё. Если таблица имя_целевой_таблицы является секцией и какая-либо входная строка нарушит ограничение секции, произойдёт ошибка.

  Имена столбцов нельзя указывать более одного раза. Действия INSERT не могут содержать вложенные запросы SELECT .

  Предложение VALUES может указываться только один раз. Ссылаться в нём можно только на столбцы исходного отношения, так как соответствующих целевых строк нет по определению. изменение_при_объединении

  Указание действия UPDATE , изменяющего текущую строку таблицы имя_целевой_таблицы . Имена столбцов нельзя указывать более одного раза.

  Задавать имя таблицы и предложение WHERE здесь нельзя. удаление_при_объединении

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

  Имя столбца таблицы имя_целевой_таблицы . При необходимости имя столбца можно дополнить именем поля или индексом массива. (При добавлении данных лишь в некоторые поля составного типа другие поля будут содержать NULL.) Имя таблицы в указание целевого столбца добавлять не нужно. OVERRIDING SYSTEM VALUE

  Без этого предложения не допускается задание явного значения (отличного от DEFAULT ) для столбца идентификации, определённого с характеристикой GENERATED ALWAYS . Данное предложение перекрывает это ограничение. OVERRIDING USER VALUE

  Если указывается это предложение, то значения, заданные для столбцов идентификации, которые определены с характеристикой GENERATED BY DEFAULT , игнорируются и вместо них применяются значения, выдаваемые последовательностями по умолчанию. DEFAULT VALUES

  Все столбцы получают значения по умолчанию. (Предложение OVERRIDING в этой форме не допускается.) выражение

  Выражение, результат которого присваивается столбцу. В выражениях предложений WHEN MATCHED могут использоваться значения из исходной строки целевой таблицы и значения из строки источника_данных . В выражениях предложений WHEN NOT MATCHED могут использоваться значения только из источника_данных . DEFAULT

  Присвоить столбцу значение по умолчанию (или NULL , если выражение по умолчанию для столбца не определено). запрос_WITH

  Предложение WITH позволяет задать один или несколько подзапросов, на которые затем можно ссылаться по имени в запросе MERGE . За подробностями обратитесь к Разделу 7.8 и SELECT .

  Выводимая информация

  При успешном выполнении команда MERGE возвращает метку команды в виде

  MERGE общее_число 

  Здесь общее_число — суммарное количество изменённых строк (добавленных, изменённых или удалённых). Если общее_число равно 0, ни одна строка не была изменена.

  Замечания

  В ходе выполнения MERGE производятся следующие действия.

  Вызываются все триггеры BEFORE STATEMENT для всех указанных действий, независимо от того, совпадают ли их предложения WHEN .

  Выполняется соединение исходной таблицы с целевой. Полученный в результате запрос оптимизируется как обычно и выдаёт набор строк-кандидатов на изменение. Для каждой строки-кандидата на изменение:

  Для каждой строки определяется состояние: MATCHED (совпадает) или NOT MATCHED (не совпадает).

  Проверяется каждое условие WHEN в заданном порядке, пока какое-либо не выдаст значение true.

  Если условие выдаёт true, происходит следующее:

  Вызываются все триггеры BEFORE ROW , соответствующие типу события выполняемого действия.

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

  То есть триггеры уровня оператора для некоторого события (скажем, INSERT ) будут вызываться всегда, когда указывается действие такого типа. Триггеры уровня строк, напротив, вызываются только для определённого действия, которое выполняется . Таким образом, при выполнении MERGE могут вызываться триггеры уровня оператора как для UPDATE , так и для INSERT , даже если на уровне строк вызывались только триггеры UPDATE .

  Следует позаботиться о том, чтобы для каждой целевой строки в результате соединения создавалось не более одной строки-кандидата на изменение. Другими словами, целевая строка не должна соединяться с более чем одной строкой источника данных. Если это не так, только одна из строк-кандидатов будет применяться для изменения целевой строки; последующие попытки изменить эту строку вызовут ошибку. Ошибка также может произойти, когда триггеры строк вносят изменения в целевую таблицу, а команда MERGE впоследствии воздействует на уже изменённые строки. Если повторится действие INSERT , это вызовет нарушение уникальности, а повторение UPDATE или DELETE вызовет ошибку « Нарушение количества » ; последнее требуется стандартом SQL . Такое поведение отличается от поведения соединений в UPDATE и DELETE , традиционного для PostgreSQL , когда вторая и последующие попытки изменить одну и ту же строку просто игнорируются.

  Если в предложении WHEN отсутствует дополнительное условие AND , оно становится последним достижимым предложением этого рода ( MATCHED или NOT MATCHED ). Если в команде встретится последующее предложение WHEN такого рода, оно гарантированно будет недостижимым, и это вызовет ошибку. В случае отсутствия последнего достижимого предложения любого рода возможна ситуация, когда для строки-кандидата на изменение не будет предпринято никаких действий.

  Порядок, в котором строки выдаются из источника данных, по умолчанию не определён. Если необходим определённый порядок, например для предотвращения взаимоблокировок между параллельными транзакциями, его можно задать в исходном_запросе .

  В операторе MERGE не допускается предложение RETURNING . Действия INSERT , UPDATE и DELETE также не могут содержать предложения RETURNING или WITH .

  Когда MERGE выполняется одновременно с другими командами, изменяющими целевую таблицу, применяются обычные правила изоляции транзакций; поведение на каждом уровне изоляции описано в Разделе 13.2. В качестве альтернативы можно рассмотреть использование оператора INSERT . ON CONFLICT , который предусматривает возможность выполнения команды UPDATE , если параллельно выполняется команда INSERT . Эти два типа операторов имеют ряд различий и особых ограничений, они не являются взаимозаменяемыми.

  Примеры

  Корректировка клиентских счетов ( customer_accounts ) с учётом новых транзакций ( recent_transactions ).

  MERGE INTO customer_account ca USING recent_transactions t ON t.customer_id = ca.customer_id WHEN MATCHED THEN UPDATE SET balance = balance + transaction_value WHEN NOT MATCHED THEN INSERT (customer_id, balance) VALUES (t.customer_id, t.transaction_value);

  Заметьте, что это полностью равнозначно следующему оператору, потому что статус MATCHED не меняется во время выполнения.

  MERGE INTO customer_account ca USING (SELECT customer_id, transaction_value FROM recent_transactions) AS t ON t.customer_id = ca.customer_id WHEN MATCHED THEN UPDATE SET balance = balance + transaction_value WHEN NOT MATCHED THEN INSERT (customer_id, balance) VALUES (t.customer_id, t.transaction_value);

  Обработка изменений количества товара: новая позиция добавляется вместе с количеством; если данная позиция уже существует, её количество корректируется; позиции с нулевым количеством удаляются.

  MERGE INTO wines w USING wine_stock_changes s ON s.winename = w.winename WHEN NOT MATCHED AND s.stock_delta > 0 THEN INSERT VALUES(s.winename, s.stock_delta) WHEN MATCHED AND w.stock + s.stock_delta > 0 THEN UPDATE SET stock = w.stock + s.stock_delta WHEN MATCHED THEN DELETE;

  Таблица wine_stock_changes может быть, например, временной таблицей, недавно загруженной в базу данных.

  Совместимость

  Эта команда соответствует стандарту SQL .

  Предложение WITH и действие DO NOTHING являются расширениями стандарта SQL .

  Пред.	Наверх	След.
  LOCK	Начало	MOVE

  Операция MERGE в языке Transact-SQL – описание и примеры

  В языке Transact-SQL в одном ряду с такими операциями как INSERT (вставка), UPDATE (обновление), DELETE (удаление) стоит операция MERGE (слияние), которая в некоторых случаях может быть полезна, но некоторые почему-то о ней не знают и не пользуются ею, поэтому сегодня мы рассмотрим данную операцию и разберем примеры.

  

  Начнем мы, конечно же, с небольшой теории.

  Заметка! Начинающим рекомендую посмотреть мой видеокурс по T-SQL.

  Что такое MERGE в T-SQL?

  MERGE – операция в языке T-SQL, при которой происходит обновление, вставка или удаление данных в таблице на основе результатов соединения с данными другой таблицы или SQL запроса. Другими словами, с помощью MERGE можно осуществить слияние двух таблиц, т.е. синхронизировать их.

  В операции MERGE происходит объединение по ключевому полю или полям основной таблицы (в которой и будут происходить все изменения) с соответствующими полями другой таблицы или результата запроса. В итоге если условие, по которому происходит объединение, истина (WHEN MATCHED), то мы можем выполнить операции обновления или удаления, если условие не истина, т.е. отсутствуют данные (WHEN NOT MATCHED), то мы можем выполнить операцию вставки (INSERT добавление данных), также если в основной таблице присутствуют данные, которое отсутствуют в таблице (или результате запроса) источника (WHEN NOT MATCHED BY SOURCE), то мы можем выполнить обновление или удаление таких данных.

  В дополнение к основным перечисленным выше условиям можно указывать «Дополнительные условия поиска», они указываются через ключевое слово AND.

  Упрощённый синтаксис MERGE

  MERGE USING ON [ WHEN MATCHED [ AND ] THEN [ WHEN NOT MATCHED [ AND Доп. условие> ] THEN ] [ WHEN NOT MATCHED BY SOURCE [ AND ] THEN ] [ . n ] [ OUTPUT ] ;

  Важные моменты при использовании MERGE:

  • В конце инструкции MERGE обязательно должна идти точка с запятой (;) иначе возникнет ошибка;
  • Должно быть, по крайней мере, одно условие MATCHED;
  • Операцию MERGE можно использовать совместно с CTE (обобщенным табличным выражением);
  • В инструкции MERGE можно использовать ключевое слово OUTPUT, для того чтобы посмотреть какие изменения были внесены. Для идентификации операции здесь в OUTPUT можно использовать переменную $action;
  • На все операции к основной таблице, которые предусмотрены в MERGE (удаления, вставки или обновления), действуют все ограничения, определенные для этой таблицы;
  • Функция @@ROWCOUNT, если ее использовать после инструкции MERGE, будет возвращать общее количество вставленных, обновленных и удаленных строк;
  • Для того чтобы использовать MERGE необходимо разрешение на INSERT, UPDATE или DELETE в основной таблице, и разрешение SELECT для таблицы источника;
  • При использовании MERGE необходимо учитывать, что все триггеры AFTER на INSERT, UPDATE или DELETE, определенные для целевой таблицы, будут запускаться.

  А теперь переходим к практике. И для начала давайте определимся с исходными данными.

  Исходные данные для примеров операции MERGE

  У меня в качестве SQL сервера будет выступать Microsoft SQL Server 2016 Express. На нем есть тестовая база данных, в которой я создаю тестовые таблицы, например, с товарами: TestTable – это у нас будет целевая таблица, т.е. та над которой мы будем производить все изменения, и TestTableDop – это таблица источник, т.е. данные в соответствии с чем, мы будем производить изменения.

  Запрос для создания таблиц.

  --Целевая таблица CREATE TABLE dbo.TestTable( ProductId INT NOT NULL, ProductName VARCHAR(50) NULL, Summa MONEY NULL, CONSTRAINT PK_TestTable PRIMARY KEY CLUSTERED (ProductId ASC) ) --Таблица источник CREATE TABLE dbo.TestTableDop( ProductId INT NOT NULL, ProductName VARCHAR(50) NULL, Summa MONEY NULL, CONSTRAINT PK_TestTableDop PRIMARY KEY CLUSTERED (ProductId ASC) )

  Далее я их наполняю тестовыми данными.

  --Добавляем данные в основную таблицу INSERT INTO dbo.TestTable (ProductId,ProductName,Summa) VALUES (1, 'Компьютер', 0) GO INSERT INTO dbo.TestTable (ProductId,ProductName,Summa) VALUES (2, 'Принтер', 0) GO INSERT INTO dbo.TestTable (ProductId,ProductName,Summa) VALUES (3, 'Монитор', 0) GO --Добавляем данные в таблицу источника INSERT INTO dbo.TestTableDop (ProductId,ProductName,Summa) VALUES (1, 'Компьютер', 500) GO INSERT INTO dbo.TestTableDop (ProductId,ProductName,Summa) VALUES (2, 'Принтер', 300) GO INSERT INTO dbo.TestTableDop (ProductId,ProductName,Summa) VALUES (4, 'Монитор', 400) GO

  Посмотрим на эти данные.

  SELECT * FROM dbo.TestTable SELECT * FROM dbo.TestTableDop

  

  Видно, что в целевой таблице значение поля Summa = 0, а также есть несоответствие некоторых идентификаторов, т.е. у нас есть товары, которые есть в одной таблице, при этом они отсутствуют в другой.

  Пример 1 – обновление и добавление данных с помощью MERGE

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

  MERGE dbo.TestTable AS T_Base --Целевая таблица USING dbo.TestTableDop AS T_Source --Таблица источник ON (T_Base.ProductId = T_Source.ProductId) --Условие объединения WHEN MATCHED THEN --Если истина (UPDATE) UPDATE SET ProductName = T_Source.ProductName, Summa = T_Source.Summa WHEN NOT MATCHED THEN --Если НЕ истина (INSERT) INSERT (ProductId, ProductName, Summa) VALUES (T_Source.ProductId, T_Source.ProductName, T_Source.Summa) --Посмотрим, что мы сделали OUTPUT $action AS [Операция], Inserted.ProductId, Inserted.ProductName AS ProductNameNEW, Inserted.Summa AS SummaNEW, Deleted.ProductName AS ProductNameOLD, Deleted.Summa AS SummaOLD; --Не забываем про точку с запятой --Итоговый результат SELECT * FROM dbo.TestTable SELECT * FROM dbo.TestTableDop

  

  Мы видим, что у нас было две операции UPDATE и одна INSERT. Так оно и есть, две строки из таблицы TestTable соответствуют двум строкам в таблице TestTableDop, т.е. у них один и тот же ProductId, у данных строк в таблице TestTable мы обновили поля ProductName и Summa. При этом в таблице TestTableDop есть строка, которая отсутствует в TestTable, поэтому мы ее и добавили через INSERT.

  Пример 2 – синхронизация таблиц с помощью MERGE

  Теперь, допустим, нам нужно синхронизировать таблицу TestTable с таблицей TestTableDop, для этого мы добавим еще одно условие WHEN NOT MATCHED BY SOURCE, суть его в том, что мы удалим строки, которые есть в TestTable, но нет в TestTableDOP. Но для начала, для того чтобы у нас все три условия отработали (в частности WHEN NOT MATCHED) давайте в таблице TestTable удалим строку, которую мы добавили в предыдущем примере. Также здесь я в качестве источника укажу запрос, чтобы Вы видели, как можно использовать запросы в качестве источника.

  --Удаление строки с ProductId = 4 --для того чтобы отработало условие WHEN NOT MATCHED DELETE dbo.TestTable WHERE ProductId = 4 --Запрос MERGE для синхронизации таблиц MERGE dbo.TestTable AS T_Base --Целевая таблица --Запрос в качестве источника USING (SELECT ProductId, ProductName, Summa FROM dbo.TestTableDop) AS T_Source (ProductId, ProductName, Summa) ON (T_Base.ProductId = T_Source.ProductId) --Условие объединения WHEN MATCHED THEN --Если истина (UPDATE) UPDATE SET ProductName = T_Source.ProductName, Summa = T_Source.Summa WHEN NOT MATCHED THEN --Если НЕ истина (INSERT) INSERT (ProductId, ProductName, Summa) VALUES (T_Source.ProductId, T_Source.ProductName, T_Source.Summa) --Удаляем строки, если их нет в TestTableDOP WHEN NOT MATCHED BY SOURCE THEN DELETE --Посмотрим, что мы сделали OUTPUT $action AS [Операция], Inserted.ProductId, Inserted.ProductName AS ProductNameNEW, Inserted.Summa AS SummaNEW,Deleted.ProductName AS ProductNameOLD, Deleted.Summa AS SummaOLD; --Не забываем про точку с запятой --Итоговый результат SELECT * FROM dbo.TestTable SELECT * FROM dbo.TestTableDop

  

  В итоге мы видим, что у нас таблицы содержат одинаковые данные. Для этого мы выполнили две операции UPDATE, одну INSERT и одну DELETE. При этом мы использовали всего одну инструкцию MERGE.

  Пример 3 – операция MERGE с дополнительным условием

  Сейчас давайте выполним запрос похожий на запрос, который мы использовали в примере 1, только добавим дополнительное условие на обновление данных, например, мы будем обновлять TestTable только в том случае, если поле Summa, в TestTableDop, содержит какие-нибудь данные (например, мы не хотим использовать некорректные значения для обновления). Для того чтобы было видно, как отработало это условие, давайте предварительно очистим у одной строки в таблице TestTableDop поле Summa (поставим NULL).

  --Очищаем поле сумма у одной строки в TestTableDop UPDATE dbo. TestTableDop SET Summa = NULL WHERE ProductId = 2 --Запрос MERGE MERGE dbo.TestTable AS T_Base --Целевая таблица USING dbo.TestTableDop AS T_Source --Таблица источник ON (T_Base.ProductId = T_Source.ProductId) --Условие объединения --Если истина + доп. условие отработало (UPDATE) WHEN MATCHED AND T_Source.Summa IS NOT NULL THEN UPDATE SET ProductName = T_Source.ProductName, Summa = T_Source.Summa WHEN NOT MATCHED THEN --Если НЕ истина (INSERT) INSERT (ProductId, ProductName, Summa) VALUES (T_Source.ProductId, T_Source.ProductName, T_Source.Summa) --Посмотрим, что мы сделали OUTPUT $action AS [Операция], Inserted.ProductId, Inserted.ProductName AS ProductNameNEW, Inserted.Summa AS SummaNEW, Deleted.ProductName AS ProductNameOLD, Deleted.Summa AS SummaOLD; --Не забываем про точку с запятой --Итоговый результат SELECT * FROM dbo.TestTable SELECT * FROM dbo.TestTableDop

  

  В итоге у меня обновилось всего две строки, притом, что все три строки успешно выполнили условие объединения, но одна строка не обновилась, так как сработало дополнительное условие Summa IS NOT NULL, потому что поле Summa у строки с ProductId = 2, в таблице TestTableDop, не содержит никаких данных, т.е. NULL.

  Заметка! Для комплексного изучения языка SQL и T-SQL рекомендую посмотреть мои видеокурсы по T-SQL, которые помогут Вам «с нуля» научиться работать с SQL и программировать на T-SQL в Microsoft SQL Server.

  На этом у меня все, удачи!

  Читать:

  Как посмотреть сохраненные пароли xiaomi в телефоне