Merge ms sql пример: Ничего не найдено для Obucheniest 561 Merge In T Sql %23Chto Takoe Merge V T Sql

Объясните оператор Merge SQL простыми словами?

Я читаю тему msdn о MERGE. http://msdn.microsoft.com/en-us/library/bb510625.aspx

Но это очень запутанно для меня. Допустим у меня есть столик,

DECLARE @T Table
(
    ID INt,
    Name VARCHAR(10)
)

и у меня есть,

MERGE INTO @T T1
USING (SELECT 4 AS ID) T2 ON (T1.ID = T2.ID)

WHEN MATCHED THEN будет выполняться каждый раз, когда совпадение найдено, WHEN NOT MATCHED THEN будет выполняться каждый раз, когда совпадение не найдено? Правильно ли это? А как насчет WHEN NOT MATCHED BY SOURCE и WHEN NOT MATCHED BY Target ? Пожалуйста, помогите мне

sql

tsql

sql-server-2008-r2

Поделиться

Источник

user960567    

03 ноября 2013 в 15:19

4 ответа

1

Слияние в основном будет объединять данные двух таблиц. Если поле ID из исходной таблицы существует в целевой таблице, оно будет выполнять оператор UPDATE ( WHEN MATCHED THEN ). в случае, если оно не существует, оно будет выполнять оператор INSERT ( WHEN NOT MATCHED BY TARGET ).

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

Прежде чем иметь оператор MERGE , нужно было бы написать эту логику самостоятельно.

Поделиться

SchmitzIT    

03 ноября 2013 в 16:06

1

Вы берете исходную таблицу и пытаетесь объединить новые записи в эту исходную таблицу. Вы устанавливаете условие для проверки на основе исходных данных и новых данных . Если условие истинно, то это совпадение, если не истинно, то это не соответствует, и у каждого будет свое собственное утверждение.

Наиболее часто используется (с sql я seen/used), когда хочу решить, добавляете ли вы новые данные в источник с помощью вставки или обновляете существующие записи. Например, инвентаризация продуктов, вы можете добавлять новые запасы или обновлять количество существующих запасов.

Здесь есть хороший пример для подражания https://www.simple-talk.com/sql/learn-sql-server/the-merge-statement-in-sql-server-2008/

Поделиться

christiandev    

03 ноября 2013 в 16:07

1

Я думаю об этом с точки зрения ветвления записей как из target , так и из source в разные пути выполнения.

Ниже я приведу пример простого списка чисел. Я использую full join для представления слияния и case для представления ‘branching’.

DECLARE @source TABLE ( i INT, c CHAR(1) )
DECLARE @target TABLE ( i INT )

INSERT INTO @source ( i )
VALUES (1), (2), (3), (4), (5)

INSERT INTO @target ( i )
VALUES (1), (2), (3), (6), (7)

SELECT 
    [source] = s.i, 
    [target] = t.i,
    [branch] = CASE WHEN t.i IS NULL THEN 'not matched by target'
                    WHEN s.i IS NULL THEN 'not matched by source'
                    ELSE 'matched' END,
    [possible action] = CASE WHEN t.i IS NULL THEN 'insert into target'
                             WHEN s.i IS NULL THEN 'update target or delete from target'
                             ELSE 'update target or delete from target' END
FROM @source s
FULL JOIN @target t ON t.i = s.i

Это приводит к следующему

source      target      branch                 possible action
----------- ----------- ---------------------  -----------------------------------
1           1           matched                update target or delete from target
2           2           matched                update target or delete from target
3           3           matched                update target or delete from target
4           NULL        not matched by target  insert into target
5           NULL        not matched by target  insert into target
NULL        6           not matched by source  update target or delete from target
NULL        7           not matched by source  update target or delete from target

так

• Если исходная запись не имеет совпадения в целевом объекте (not matched by target ) , то они могут быть insert ed в целевом объекте
• Если целевая запись не имеет совпадения в источнике (not matched by source), то соответствующая целевая запись может быть update d или delete d здесь, очевидно, не будет никаких исходных записей для ссылки.
• Когда исходная запись совпадает с целевой записью (matched), то целевая запись также может быть update d или delete d, но в отличие от not matched by source здесь у вас также будут записи из источника.

Примечание. для обновлений и удалений нет необходимости использовать соединение или иным образом связывать источник с целью, цель с целью и т. д. В пределах ‘branch’, поскольку эти отношения уже разрешены, и это похоже на то, как если бы вы действовали на отдельной записи.

например, вы можете подумать, что вам придется сделать обновление как

 Update t
 Set t.col = s.col
 From target t
 Join source s On s.id = t.id

но это не тот случай.

Если запись была либо matched , либо not matched by source , то можно дополнительно предикировать данные, чтобы решить, должна ли она быть delete d или update d. Это делается путем предоставления двух одинаковых ‘branch’ с дополнительным предложением AND , как показано в example d из MERGE .

Поделиться

T I    

03 ноября 2013 в 16:38

0

Последние два должны быть очевидны. Если совпадение не найдено, это означает, что в одной или другой таблице нет строки, где есть другая таблица. То есть у T1 может быть строка, но нет соответствующей строки в T2, или у T2 может быть строка, но нет соответствующей строки в T1. T2-это источник, а T1-цель, поэтому, когда вы говорите NOT MATCHED BY SOURCE , вы указываете действие, которое должно произойти, когда у T2 нет строки, а NOT MATCHED BY TARGET -когда у T1 нет строки.

Поделиться

siride    

03 ноября 2013 в 16:01

Похожие вопросы:

Объясните xpath и xquery простыми словами

Я новичок в программировании. Я знаю, что такое XML. Может ли кто — нибудь объяснить простыми словами, что делают xpath и xquery, где они используются?

Обрабатывает ли задача SSIS Execute SQL оператор MERGE?

Я пытаюсь выполнить оператор MERGE с помощью задачи Execute SQL, как описано в этой статье: http://technet.microsoft.com/en-us/library/cc280522.aspx Мое утверждение MERGE довольно просто(см. Я могу…

Что такое функция потерь простыми словами?

Может ли кто — нибудь объяснить простыми словами и, возможно, с некоторыми примерами, что такое функция потерь в области машинного обучения/нейронных сетей? Это вышло, когда я следовал учебнику…

Могу ли я использовать оператор MERGE в SQL Server 2005?

Я использую SQL Server 2005, и я хотел создать MERGE оператор или концепцию в одном запросе в SQL Server 2005. Возможно ли это?

T SQL merge пример, необходимый для понимания

Следующий: MERGE dbo.commissions_history AS target USING (SELECT @amount, @requestID) AS source (amount, request) ON (target. request = source.request) WHEN MATCHED THEN UPDATE SET amount =…

Obj-C, UINavigationControllers внутри a UITabbarController, объясните простыми словами?

Я играю с некоторым образцом кода, чтобы попытаться раз и навсегда выяснить, как заставить навигационный контроллер (Ы) и контроллер вкладок работать вместе. В качестве бонуса без утечек памяти….

Что такое контейнер Laravel IoC простыми словами?

Может ли кто-нибудь объяснить инъекцию зависимостей и контейнер IoC простыми и легкими словами, так как я новичок в laravel.

HQL внутреннее соединение-пожалуйста, объясните простыми словами

В учебнике HQL он приводит следующий пример: select s.name, p.name, p.price from Product p inner join p.supplier as s Продукт явно является одной из сущностей, и похоже, что он присоединяется к…

Объясните ‘Multi-col-linearity’ в обработке данных

Может ли кто-нибудь объяснить термин Multi-col-linearity из обработки данных машинного обучения простыми словами? Поскольку этот термин очень важен по отношению к обработке данных и имеет запутанные. ..

Node.js — что означает “socket hang up” простыми словами?

Я хотел бы знать, что означает socket hang up очень простыми словами.

Merge, SQL. Merge — оператор языка SQL, который позволяет слить данные одной таблицы с данными другой таблицы. При слиянии таблиц проверяется условие, и если он

Пользователи также искали:

merge into,

merge postgresql,

merge sql habr,

merge sql oracle,

merge sql примеры,

merge sql tutorial,

oracle sql merge пример,

sql merge two tables,

Merge,

merge,

Merge SQL,

примеры,

oracle,

merge sql примеры,

merge sql tutorial,

merge postgresql,

sql merge two tables,

merge into,

oracle sql merge пример,

into,

merge sql habr,

habr,

tutorial,

postgresql,

tables,

пример,

merge sql oracle,

merge (sql),

cтатьи о компьютерных языках. merge (sql),

ITband.ru » Безопасные скрипты по-новому

SQL Server 2008 содержит несколько давно ожидаемых расширений синтаксиса, которые облегчают написание скриптов вообще и написание безопасных скриптов в частности. Под безопасными скриптами мы будем понимать перезапускаемые скрипты, которые проверяют состояние данных перед тем как вносить изменения, и изменяют только нуждающиеся в изменениях данные. Например, перед добавлением новой строки будет произведена проверка наличия строки в таблице, и если ее нет, она будет добавлена. В предыдущей статье была кратко затронута тема написания безопасных скриптов с использованием “классического” TSQL. В этой статье мы рассмотрим, что предлагает SQL Server 2008 для облегчения задачи написания таких скриптов.

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

Безопасным мы будем называть скрипт, который производит изменения в БД особым способом, делая проверки перед выполнением операторов. Например, при добавлении одиночной строки, мы проверяем ее наличие с помощью функции exists. Если мы добавляем множество строк, мы создаем временную таблицу, помещаем в нее строки, затем вставляем их одним оператором INSERT, вставляя только ранее не вставленные строки.

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

Об удалении дополнительно заботится не надо, операторы удаления безопасны.

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

Безопасные скрипты можно легко комбинировать друг с другом и интегрировать их в автоматические или полу-автоматические build/deployment системы.

Не так уж сложно, но написание безопасных скриптов никак не назовешь веселым занятием. Обычно это происходит так. Вначале пишется базовый скрипт, который решает поставленную задачу. Он тестируется и затем переводится в безопасный вид. Перевод в безопасный вид – достаточно механический процесс, не требующий больших раздумий, изобилующий операциями копирования и вставки. Как и всякий подобный процесс, он может приводить к ошибкам. После преобразования скрипт снова тестируется.

В новой версии сервера появились языковые средства, упрощающие процесс. Теперь не нужен этап преобразования, скрипт сразу можно писать и тестировать в безопасном виде. Рассмотрим эти нововведения.

Конструкторы VALUES

Если мы добавляем множество строк, нам нужно создать, заполнить и удалить временную таблицу. Это несложно, но это лишний код. Лучшим решением было бы объявление таблицы и данных непосредственно в тексте запроса. Здесь есть прямая аналогия с инициализаторами в C#. Можно объявить экземпляр класса, создать его, присвоить значения его полям, затем использовать для чего-либо. Или можно создать экземпляр, одновременно определяя поля. Второй вариант записывается намного короче и нагляднее. Так же и с таблицами.

Если раньше нам приходилось писать так:

create table #tmp(ID int not null primary key, Name nvarchar(10))
insert into #tmp values (1, ‘aaa’)
insert into #tmp values (2, ‘bbb’)

–use #tmp
select * from #tmp

drop table #tmp

Или хитрить с помощью UNION ALL.

insert into #tmp
select 1 ID, ‘aaa’ Name
union all
select 2, ‘bbb’

То теперь мы можем обойтись одним коротким оператором.

insert into #tmp
values
   (1, ‘aaa’),
   (2, ‘bbb’)

Или можем обойтись вообще без предварительного объявления таблицы

select *
from
 (values
   (1, ‘aaa’),
   (2, ‘bbb’)
 ) a (ID, Name)

Так сформированную таблицу можно использовать в безопасном INSERT:

INSERT INTO t
SELECT a.*
FROM
 (VALUES
   (1, ‘aaa’),
   (2, ‘bbb’),
   (3, ‘ccc’)
 ) a (ID, Name)
left join BaseTable t
   on a. ID = t.ID
WHERE
t.ID is null

Слияние данных

При безопасном добавлении мы проверяем наличие данных, и добавляем только отсутствующие строки. Также часто бывает нужно обновить уже существующие строки. Первая задача решается INSERT с соответствующей проверкой, вторая – UPDATE. Итого нам надо два оператора для одной строки. Если строк несколько, то нам потребуется создать временную таблицу, а затем использовать ее в операторах INSERT и UPDATE. Уже очень давно программистское сообщество обсуждает идею оператора UPSERT, который совместит в себе две операции – добавит строку, если ее нет, и обновит если она есть. Кроме безопасных скриптов, подобный оператор был бы очень удобным во многих сценариях работы с БД. Например, при работе с денормализованными таблицами, когда нам надо вставлять\обновлять строки при каждой операции с базовыми таблицами, или для написания триггеров. Кроме UPSERT, в зависимости от сценария использования данных, были бы удобны и другие комбинации. Например UPDATE\DELETE – обновить существующие, удалить отсутсвующие. Также пригодилась бы и комбинация всех трех операторов – INSERT\UPDATE\DELETE. Наверное по этой причине создатели сервера реализовали универсальный оператор MERGE, который умеет делать все три операции по определенным нами правилам.

В отличие от универсальных операторов INSERT\UPDATE\DELETE, оператор MERGE удобен для использования в определенных сценариях. В этих сценариях у нас есть две таблицы. Первая – это базовая таблица, которая содержит основное множество строк. Вторая (дополнительная или временная) – эта таблица с такой же структурой как и первая, содержащая новые\измененные строки. Задачей оператора является соединение (merge) данных из дополнительной и базовой таблиц. После выполнения оператора базовая таблица будет обновлена в соответствии с заданными правилами (например, новые строки добавить, старые обновить).

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

1. Строки, которые есть как в базовой, так и в дополнительной таблице (MATCHED). К этой группе мы можем применить два действия – UPDATE и DELETE, т.е. обновить существующие строки, либо удалить их.
2. Строки, которые есть только в дополнительной таблице, но отсутсвуют в базовой (NOT MATCHED BY TARGET). Для этой группы мы можем сделать только INSERT.
3. Строки, которые есть только в базовой таблице, но отсутсвуют в дополнительной (NOT MATCHED BY SOURCE). Такие строки мы можем либо обновить, либо удалить.

Эти три множества позволят нам задать правила обработки строк. Стоит отметить, что оператор реализован так, что для каждой строки во временной таблице должна существовать только одна строка в базовой. Оператор не умеет обновлять несколько строк в базовой таблице по одной строке во временной.

Упрощенный синтаксис оператора таков:

01 MERGE <target_table>
02    USING <table_source>
03    ON <merge_search_condition>
04    [ WHEN MATCHED
05        THEN UPDATE SET <set_clause> | DELETE ]
06    [ WHEN NOT MATCHED [ BY TARGET ]
07        THEN INSERT ]
08    [ WHEN NOT MATCHED BY SOURCE
09        THEN UPDATE SET <set_clause> | DELETE ]
10 ;

В первой строке мы задаем базовую таблицу, во второй строке – дополнительную. В ON задаем предикат для сравнения, например BaseTable.ID = TempTable.ID. Дальше следует нечто вроде оператора выбора, где мы указываем что делать с группой строк. Например, WHEN NOT MATCHED BY TARGET THEN INSERT означает – добавить строки, если их нет в базовой таблице. Обратите внимание, что точка с запятой после этого оператора обязательна.

Рассмотрим простейший пример.

MERGE BaseTable b
USING #tmp t
ON b. ID = t.ID
WHEN MATCHED THEN
   UPDATE SET Name = t.Name
WHEN NOT MATCHED BY TARGET THEN
   INSERT VALUES (ID, Name)
;

Мы выполняем совмещение таблицы baseTable с #tmp, соединяя их по полю ID. Для общих строк будет обновлено поле Name, новые строки будут добавлены.

Как реализован данный оператор? Надо заметить, что оператор MERGE – это так называемый синтаксический сахар, то есть синтаксическое средство для более удобной записи. Он не добавляет чего-либо нового, ту же самую функциональность можно реализовать с помощью “классических” языковых средств. Если в операторе MERGE содержится только часть WHEN MATCHED, то сервер будет выполнять обычное соединение (JOIN). Если есть WHEN NOT MATCHED BY TARGET или NOT MATCHED BY SOURCE, то будет выполнятся LEFT\RIGHT JOIN. Если есть оба NOT MATCHED, то будет выполнен FULL JOIN. Об этом нужно помнить и следить за размерностью результата. Если у нас есть только часть MATCHED, то результат соединения будет содержать только совпадающие строки из двух таблиц. Так как временная таблица обычно невелика, то и результат будет небольшим. Если в операторе есть NOT MATCHED BY TARGET, мы получим в результате соединения как минимум столько же строк, сколько их есть во временной таблице. Соединение выполняемое при NOT MATCHED BY SOURCE вернет все строки из базовой таблицы, а их может быть очень много. Ну а наличие обоих частей может вернуть еще больше строк. К счастью, мы можем дописывать условия к правилам, ограничивая количество строк в результате. Например, не надо обновлять не изменившиеся имена:

MERGE BaseTable b
USING #tmp t
 ON b.ID = t.ID
 WHEN MATCHED AND b.Name <> t.Name
   THEN
   UPDATE SET Name = t.Name
 WHEN NOT MATCHED BY TARGET THEN
   INSERT VALUES (ID, Name)
;

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

Дополнительные возможности MERGE

Рассмотренной функциональности MERGE хватит для написания безопасных скриптов, но у оператора есть еще козыри в рукаве. Во-первых, в операторе можно указывать части MATCHED и NOT MATCHED BY SOURCE дважды, по одному разу для каждого действия (обе части допускают UPDATE и DELETE). При этом первый записанный селектор должен содержать дополнительное условие. При выполнении второй селектор будет проверятся, только если не выполнился первый. Например, мы можем записать

WHEN MATCHED AND b.IsActual=0 THEN DELETE
WHEN MATCHED UPDATE SET …

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

Вторая дополнительная возможность – расширенный оператор OUTPUT. Так как оператор MERGE может производить все три базовые операции над данными, то в нем доступны обе таблицы, inserted и deleted, содержащие версии строк до и после изменения, также в OUTPUT можно использовать функцию $ACTION, которая возвращает INSERT, DELETE или UPDATE, в зависимости от выбранной операции над строкой. Пример:

MERGE BaseTable b
USING #tmp t
 ON b.ID = t.ID
 WHEN MATCHED AND b.Name <> t.Name
   THEN
   UPDATE SET Name = t.Name
 WHEN NOT MATCHED BY TARGET THEN
   INSERT VALUES (ID, Name)
OUTPUT t.ID, $ACTION
;

Выводом такого оператора может быть

1    INSERT
2    DELETE
3    UPDATE

Пример безопасного скрипта

Предположим, что у нас есть такая базовая таблица:

CREATE TABLE BaseTable(ID int not null primary key, Name nvarchar(10))
INSERT INTO BaseTable values (1, ‘aaa’)
INSERT INTO BaseTable values (2, ‘bbb’)

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

CREATE TABLE #tmp(ID int not null , Name nvarchar(10))

INSERT INTO #tmp values (1, ‘AAAA’)
INSERT INTO #tmp values (3, ‘ccc’)

INSERT INTO BaseTable
SELECT t.ID, t.Name
FROM #tmp t
 left join BaseTable b
   on t. ID = b.ID
WHERE
  b.ID is null

UPDATE b
SET
  b.Name = t.Name
FROM BaseTable b
 join #tmp t
   on b.ID = t.ID
WHERE
  b.Name <> t.Name

DROP TABLE #tmp

Теперь перепишем его с использованием MERGE.

MERGE BaseTable b
USING (VALUES
   (1, ‘AAAA’),
   (3, ‘ccc’)
   ) t (ID, Name)
ON b.ID = t.ID
WHEN MATCHED AND b.Name <> t.Name
   THEN
   UPDATE SET Name = t.Name
WHEN NOT MATCHED BY TARGET THEN
   INSERT VALUES (ID, Name)
;

Второй скрипт значительно короче, а делает то же самое.

Вывод

Конечно, в VALUES и MERGE нет ничего революционного, они упрощают написание скриптов в некоторых сценариях, добавляя в TSQL возможности, которые уже давно есть у конкурентов, не более. Использовать новый оператор MERGE нужно с оглядкой, хорошо представляя его работу и проверяя производительность. В некоторых сценариях он очень удобен, в других случаях старые методы имеют преимущество. Язык TSQL не может похвастать бурной историей изменений, и даже простейшие улучшения всегда вызывают теплый прием.

Александр Синицын

SQL | Community Creatio

Зачастую приложение разработанное на BPMOnline, как и последующие его доработки требуется предоставлять в виде «all inclusive», т.е. всё что нужно, в т.ч. и настройки и бэкраунд-данные должны быть в Ваших пакетах, и единственное что остается сделать клиенту или его специалистам — это установить их. Но в части механизма переноса данных в самой архитектуре приложения есть проблемные моменты, в основном часто встречающиеся юзкейсы:

1) Перенос/инсталляция элементов организационной структуры, или изменений в ней через пакет.
проблематика: Через «Данные» не переносятся, подключить их в пакет у Вас конечно получится с кучей связей, но при установке вас ожидает фиаско, в первую очередь потому, что требуется четкое соблюдение порядка добавления записей, в таблице SysAdminUnits есть внутренние связи FK между колонками Id и ParentId (значение одной колонки ссылается на значение из другой колонки этой же таблицы). Этими значениями определяется иерархия, по этому Вам необходимо добавлять записи в соответствии с их положением в дереве: от корня в глубь.
совет: организовывайте вашу структуру в мастере в том числе проставьте галочки на «Есть руководители» (т.к. создается отельный орг.юнит), после чего в любом удобном инструменте просмотра таблиц БД, в таблице SysAdminUnits отсортируйте записи по колонке «CreatedOn» в порядке возрастания, и в таком виде экспортируйте INSERT инструкции для каждой записи отдельным блоком.
PS: так же зачастую требуется перенос значений из таблиц SysAdminUnitsInRole (пользователи включенные в орг.юнит), и SysFuncRoleInOrgRole (связи орг.юнитов друг с другом),
а перед инсталяцией орг.юнитов пользователей — сначала инсталируйте контакты с которыми они будут связаны.

2) Перенос/инсталляция настроек рабочих мест, или изменений в них через пакет.
проблематика: По той причине что организационную структуру Вы через данные не переносите, настройка рабочих мест просто не даст Вам создать данные так, как вы не включаете в виде данных необходимые значения для орг.юнитов.
PS: Сами рабочие места, и даже разделы вы можете перенести через данные,
но если вам нужны данные о пользователях группах пользователей в раб.местах — тут только SQL-скрипт. Так что лучше уж тогда все переносить в скрипте.

3) Перенос/инсталляция настройки колонок в реестрах, деталях, окнах выбора и т.д.
проблематика: Через «Данные» не переносятся, т.к. содержат бинарные данные в 2-х колонках (ObjectData, ObjectDifference) таблицы SysProfileData по какой-то причине не включаются в пакет вместе с данными, и аналогично предыдущему пункту вы можете создать такие «Данные», но при инсталляции получите записи с пустыми вышеупомянутыми колонками.
совет: Опять же настраиваем «ручками в системе» потом отлавливаем новые записи в любом SQL-view, дампим записи (их будет 2-3 на каждый элемент настройки колонок) в блоках INSERT, но в данном случае не забудьте, что вам при инсталляции надо будет проверить существуют ли в таблице записи с такими же соотношениями колонок «Key» — «ContactId» и удалить их (не обновлять) если они есть после чего вставлять Ваши записи.

Вообщем у Вас может получиться объемистая часть данных которые надо включать в пакеты в виде SQL-скрипта. И идеальный подход для этого конечно использование подхода «Вставить, а если существует — обновить«.
Так вот TSQL не предлагает каких либо «сахарных» инструкций aka MySQL UPSERT или PostgreSQL ON DUPLIKATE KEY
В TSQL ближайший аналог это монстроузный MERGE который можно использовать для реализации вышеупомянутого подхода, ну конечно можно еще c использование IF EXISTS писать еще более «монструозные простыни», и даже делать SELECT->FOR-IF-UPDATE/INSERT но это уж простите меня — совсем «нубство».
Первый взгляд на MERGE вас конечно немного испугает и заставит пропотеть, особенно учитывая тот факт что вам не обойтись без многократного описания колонок и их соотношений, а в большинстве таблиц с которыми Вам его надо будет применять — более 10-ти колонок. 🙂 а Вам понадобится их перечисление в 4-х местах в разном виде.
Но на помощь нам приходят современные средства разработки !
Итак… вот вам пошаговый рецепт составления такого скрипта на примере составления MERGE для переноса/обновления из таблицы SysAdminUnits в среде Jetbrains DataGrip (если вы или Ваши друзья коллеги студенты IT-смежной специальности, Вы без проблем получите ключ на год на весь пантеон из продуктов, WebStrom просто незаменим для JavaScript, особенно в свете того что с 7.10 версии наконец-то можно работать с пакетами в файловом режиме)
PS: наверняка в SQL Managment Studio можно обвеситься плагинами и получить аналогичную функциональность (я сейчас про некоторые хоткеи и мультикурсорность, которые и есть суть — все сильно упрощают), если это так — прошу знатоков отписаться в комментариях что для этого потребуется, или же подтвердить что там так не выйдет.
Итак поехали:
Копируем шаблон конструкции

DROP TABLE IF EXISTS #Temp;
CREATE TABLE #Temp
(

);

MERGE TargetTable AS dst
USING #Temp AS src
ON (dst.Id=src.Id)
WHEN MATCHED THEN
    UPDATE SET

WHEN NOT MATCHED BY TARGET THEN
    INSERT
    (

    )
    VALUES (

    );
GO

ДАЛЬНЕЙШИЕ ИЗОБРАЖЕНИЯ ЭТО GIF-АНИМАЦИЯ, CLICKайте и ПРОСМАТРИВАЙТЕ
Открываем необходимую таблицу в структуре и переходим к ее определению (вкладка DDL)
поиск в боковой схеме — простой набор символов с клавиатуры при выделении любой таблицы
открытие таблицы — F4

и копируем определение колонок в определение колонок временной таблицы шаблона, даем временной таблице осмысленное имя,

и вот тут начнется «магия» мультиселекта это IDE
Alt+j (установка мультикурсорности на обнаруженных паттернах выделенного фрагмента)
Нам необходимо избавиться в обявлении колонок от значений «по умолчанию» (не знаю почему, но с этим иногда бывают проблемы, в контексте нашей задачи, проще избавиться, чтобы наверняка).
А так же удалем FK определения они нам само собой тоже не нужны

При помощи мощи мультиселекта и хоткеев паттерного выделения (выделить слово, добить/убрать из выделения символ) «творим магию»

обратите внимание на то, что лишние запятые изначально оставляются чтобы все строки соответствовали паттерну, а потом удаляются, остается установить имя целевой таблицы.
и вот мы получили вот такой вот скрипт
DROP TABLE IF EXISTS #TempSysAdminUnits;
CREATE TABLE #TempSysAdminUnits
(
  Id UNIQUEIDENTIFIER PRIMARY KEY NOT NULL,
  CreatedOn DATETIME2,
  CreatedById UNIQUEIDENTIFIER,
  ModifiedOn DATETIME2,
  ModifiedById UNIQUEIDENTIFIER,
  Name NVARCHAR(250) NOT NULL,
  Description NVARCHAR(250) NOT NULL,
  ParentRoleId UNIQUEIDENTIFIER,
  ContactId UNIQUEIDENTIFIER,
  TimeZoneId NVARCHAR(250) NOT NULL,
  UserPassword NVARCHAR(250) NOT NULL,
  SysAdminUnitTypeValue INT NOT NULL,
  AccountId UNIQUEIDENTIFIER,
  Active BIT NOT NULL,
  LoggedIn BIT NOT NULL,
  SynchronizeWithLDAP BIT NOT NULL,
  LDAPEntry NVARCHAR(250) NOT NULL,
  LDAPEntryId NVARCHAR(250) NOT NULL,
  LDAPEntryDN NVARCHAR(500) NOT NULL,
  IsDirectoryEntry BIT NOT NULL,
  ProcessListeners INT NOT NULL,
  SysCultureId UNIQUEIDENTIFIER,
  LoginAttemptCount INT NOT NULL,
  SourceControlLogin NVARCHAR(250) NOT NULL,
  SourceControlPassword NVARCHAR(250) NOT NULL,
  PasswordExpireDate DATETIME2,
  HomePageId UNIQUEIDENTIFIER,
  ConnectionType INT NOT NULL,
  UnblockTime DATETIME2,
  ForceChangePassword BIT NOT NULL,
  LDAPElementId UNIQUEIDENTIFIER,
  DateTimeFormatId UNIQUEIDENTIFIER,
);

MERGE SysAdminUnits AS dst
USING #TempSysAdminUnits AS src
ON (dst. Id=src.Id)
WHEN MATCHED THEN
    UPDATE SET
      dst.CreatedOn=src.CreatedOn,
      dst.CreatedById=src.CreatedById,
      dst.ModifiedOn=src.ModifiedOn,
      dst.ModifiedById=src.ModifiedById,
      dst.Name=src.Name,
      dst.Description=src.Description,
      dst.ParentRoleId=src.ParentRoleId,
      dst.ContactId=src.ContactId,
      dst.TimeZoneId=src.TimeZoneId,
      dst.UserPassword=src.UserPassword,
      dst.SysAdminUnitTypeValue=src.SysAdminUnitTypeValue,
      dst.AccountId=src.AccountId,
      dst.Active=src.Active,
      dst.LoggedIn=src.LoggedIn,
      dst.SynchronizeWithLDAP=src.SynchronizeWithLDAP,
      dst.LDAPEntry=src.LDAPEntry,
      dst.LDAPEntryId=src.LDAPEntryId,
      dst.LDAPEntryDN=src.LDAPEntryDN,
      dst.IsDirectoryEntry=src.IsDirectoryEntry,
      dst.ProcessListeners=src.ProcessListeners,
      dst.SysCultureId=src.SysCultureId,
      dst.LoginAttemptCount=src. LoginAttemptCount,
      dst.SourceControlLogin=src.SourceControlLogin,
      dst.SourceControlPassword=src.SourceControlPassword,
      dst.PasswordExpireDate=src.PasswordExpireDate,
      dst.HomePageId=src.HomePageId,
      dst.ConnectionType=src.ConnectionType,
      dst.UnblockTime=src.UnblockTime,
      dst.ForceChangePassword=src.ForceChangePassword,
      dst.LDAPElementId=src.LDAPElementId,
      dst.DateTimeFormatId=src.DateTimeFormatId
WHEN NOT MATCHED BY TARGET THEN
    INSERT
    (
      Id,
      CreatedOn,
      CreatedById,
      ModifiedOn,
      ModifiedById,
      Name,
      Description,
      ParentRoleId,
      ContactId,
      TimeZoneId,
      UserPassword,
      SysAdminUnitTypeValue,
      AccountId,
      Active,
      LoggedIn,
      SynchronizeWithLDAP,
      LDAPEntry,
      LDAPEntryId,
      LDAPEntryDN,
      IsDirectoryEntry,
      ProcessListeners,
      SysCultureId,
      LoginAttemptCount,
      SourceControlLogin,
      SourceControlPassword,
      PasswordExpireDate,
      HomePageId,
      ConnectionType,
      UnblockTime,
      ForceChangePassword,
      LDAPElementId,
      DateTimeFormatId
    )
    VALUES (
      src. Id,
      src.CreatedOn,
      src.CreatedById,
      src.ModifiedOn,
      src.ModifiedById,
      src.Name,
      src.Description,
      src.ParentRoleId,
      src.ContactId,
      src.TimeZoneId,
      src.UserPassword,
      src.SysAdminUnitTypeValue,
      src.AccountId,
      src.Active,
      src.LoggedIn,
      src.SynchronizeWithLDAP,
      src.LDAPEntry,
      src.LDAPEntryId,
      src.LDAPEntryDN,
      src.IsDirectoryEntry,
      src.ProcessListeners,
      src.SysCultureId,
      src.LoginAttemptCount,
      src.SourceControlLogin,
      src.SourceControlPassword,
      src.PasswordExpireDate,
      src.HomePageId,
      src.ConnectionType,
      src.UnblockTime,
      src.ForceChangePassword,
      src.LDAPElementId,
      src.DateTimeFormatId
    );
GO

потратив на его составление менее 5-ти минут 🙂
Ну а далее в этот скрипт перед операцией MERGE перенесите INSERT конструкциями, то что требуется изменив назначение на временную таблицу

Вуаля. .. итого 5-7 минут и готово.
Другими способами и копипастой, такой скриптик писать с таким огромным разношерстным объявлением — минут 20-30 🙂
Так что юзайте возможности современных средств разработки коллеги.

Каковы различия между преобразованиями Merge Join и Lookup в SSIS?

Скриншот # 1 показывает несколько моментов, чтобы различать Merge Join transform и Преобразование Lookup .

Что касается поиска:

Если вы хотите найти соответствие строк в источнике 2 на основе входа источника 1, и если вы знаете, что для каждой строки ввода будет только одно совпадение, я бы предложил использовать операцию поиска. Примером может служить таблица OrderDetails , и вы хотите найти соответствующий Order Id и Customer Number , тогда Lookup — лучший вариант.

Что касается объединения слиянием:

Если вы хотите выполнять объединения, такие как выбор всех Адресов (Главная, Работа, Другое) из таблицы Адрес для данного Клиента в таблице Customer , тогда вы должны пойти с Merge Присоединяйтесь, потому что у клиента может быть 1 или более адресов, связанных с ними.

Пример сравнения:

Ниже приведен сценарий, демонстрирующий различия в производительности между Merge Join и Lookup . Используемые здесь данные представляют собой взаимно однозначное соединение, которое является единственным сценарием, общим для сравнения.

1. У меня есть три таблицы с именем dbo.ItemPriceInfo , dbo.ItemDiscountInfo и dbo.ItemAmount . Создание сценариев для этих таблиц предоставляется в разделе SQL-скриптов.

2. Таблицы dbo.ItemPriceInfo и dbo.ItemDiscountInfo имеют 13 349 729 строк. Обе таблицы имеют ItemNumber как общий столбец. ItemPriceInfo имеет информацию о ценах, а ItemDiscountInfo имеет информацию о скидках. Скриншот # 2 показывает количество строк в каждой из этих таблиц. Скриншот # 3 показывает верхние 6 строк, чтобы дать представление о данных, присутствующих в таблицах.

3. Я создал два пакета SSIS для сравнения производительности преобразований объединения и поиска. Оба пакета должны взять информацию из таблиц dbo.ItemPriceInfo и dbo.ItemDiscountInfo , вычислить общую сумму и сохранить ее в таблице dbo.ItemAmount .

4. Первый пакет использовал преобразование Merge Join и внутри него использовал INNER JOIN для объединения данных. Скриншоты # 4 и # 5 показывают пример выполнения пакета и продолжительность выполнения. Это потребовало 05 минут 14 секунд 719 миллисекунды для выполнения пакета преобразования, основанного на объединении объединения.

5. Второй пакет использует преобразование Lookup с полным кешем (который является настройкой по умолчанию). creenshots # 6 и # 7 показывают пример выполнения пакета и продолжительность выполнения. Это потребовало 11 минут 03 секунд 610 миллисекунды для выполнения пакета преобразования, основанного на поиске. Вы можете столкнуться с предупреждающим сообщением. Информация: Менеджер буфера выделил nnnnn байты, даже несмотря на то, что было обнаружено давление памяти, и повторные попытки сброса буферов потерпели неудачу. Вот ссылка , в котором говорится о том, как рассчитать размер кеша просмотра. Во время выполнения этого пакета, хотя задача потока данных выполнялась быстрее, очистка трубопровода занимала много времени.

6. Это значение не означает . Просто его нужно использовать мудро. Я использую это довольно часто в своих проектах, но опять же я не занимаюсь поиском 10+ миллионов строк для ежедневного поиска. Обычно мои задания занимают от 2 до 3 миллионов строк, и для этого производительность действительно хороша. До 10 миллионов строк, оба выполнялись одинаково хорошо. Большую часть времени я заметил, что узкое место оказывается компонентом назначения, а не преобразованиями. Вы можете преодолеть это, имея несколько пунктов назначения. Здесь это пример, показывающий реализацию нескольких пунктов назначения.

7. Снимок экрана # 8 показывает количество записей во всех трех таблицах. Скриншот # 9 показывает первые 6 записей в каждой из таблиц.

Надеюсь, это поможет.

Сценарии SQL:

CREATE TABLE [dbo].[ItemAmount](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ItemNumber] [nvarchar](30) NOT NULL,
    [Price] [numeric](18, 2) NOT NULL,
    [Discount] [numeric](18, 2) NOT NULL,
    [CalculatedAmount] [numeric](18, 2) NOT NULL,
CONSTRAINT [PK_ItemAmount] PRIMARY KEY CLUSTERED ([Id] ASC)) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ItemDiscountInfo](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ItemNumber] [nvarchar](30) NOT NULL,
    [Discount] [numeric](18, 2) NOT NULL,
CONSTRAINT [PK_ItemDiscountInfo] PRIMARY KEY CLUSTERED ([Id] ASC)) ON [PRIMARY]
GO

CREATE TABLE [dbo]. [ItemPriceInfo](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ItemNumber] [nvarchar](30) NOT NULL,
    [Price] [numeric](18, 2) NOT NULL,
CONSTRAINT [PK_ItemPriceInfo] PRIMARY KEY CLUSTERED ([Id] ASC)) ON [PRIMARY]
GO

Снимок экрана №1:

Снимок экрана №2:

Снимок экрана №3: ​​

Снимок экрана №4:

Скриншот № 5:

Снимок экрана № 6:

Снимок экрана № 7:

Снимок экрана №8:

Снимок экрана № 9:

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

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

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

Рисунок 1 — Иллюстрация MERGE

На приведенном выше рисунке показано, как в основном работает оператор SQL MERGE. Как видите, есть два круга, которые представляют две таблицы и могут рассматриваться как источник и цель. Оператор MERGE пытается сравнить исходную таблицу с целевой таблицей на основе ключевого поля, а затем выполняет некоторую обработку.Оператор MERGE фактически объединяет операции INSERT, UPDATE и DELETE вместе. Хотя оператор MERGE немного сложнее, чем простые INSERT или UPDATE, как только вы овладеете базовой концепцией, вы можете легко использовать это SQL MERGE чаще, чем отдельные INSERT или UPDATE.

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

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

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

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

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

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

Практическое руководство с заявлением MERGE

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

Рисунок 2 — Образцы данных вставлены

Теперь, когда база данных готова, следующий шаг, который я собираюсь выполнить, — применить оператор MERGE и попытаться получить
обе таблицы синхронизировать друг с другом. Первая операция, которую мы пытаемся увидеть, — это как управлять
ВСТАВКИ. Вы можете скопировать и вставить приведенный ниже код SQL, чтобы объединить новые данные из исходной таблицы в целевую.

Рисунок 3 — Операция MERGE, выполняемая над исходной и целевой таблицами

Как видите, две записи с ProductID 3 и 4, которых не было в целевой таблице, теперь
вставлен. Эта операция выполняется путем сопоставления исходной и целевой таблиц на основе поля ProductID .

Теперь, когда мы узнали, как вставлять записи с помощью оператора SQL MERGE, давайте узнаем, как обновлять значения.
в том же заявлении.Чтобы обновить значения, поле ProductID должно иметь общее значение в обоих
исходные и целевые таблицы. Только тогда ядро ​​базы данных сможет сопоставить записи и обновление.
операция может быть выполнена для указанных столбцов.

Рисунок 4 — Запись обновлена ​​с помощью оператора MERGE

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

Давайте теперь посмотрим, как удалить или удалить записи из целевой таблицы в том же самом скрипте.

Рисунок 5 — Записи, удаленные с помощью оператора MERGE

Теперь, если вы видите, все записи с ProductID 5 и 6 удаляются из целевой таблицы, поскольку эти
записи недоступны в источнике.Таким образом, вы можете реализовать инструкцию SQL MERGE в очень простой, но
мощный способ и может справиться со сложными бизнес-требованиями.

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

Рисунок 6. Проверка выходных действий оператором слияния

Что важно помнить при реализации SQL MERGE

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

1. Каждый оператор MERGE должен заканчиваться точкой с запятой. Если точка с запятой отсутствует в конце оператора MERGE, будет выдана ошибка.
2. Вы можете использовать SELECT @@ RowCount после написания оператора MERGE, который вернет количество записей, которые были изменены транзакцией.
3. Обязательно наличие одного из предложений MATCHED, чтобы оператор MERGE работал.

Заключение

В этой статье я подробно рассказал об операторе SQL MERGE.Этот оператор MERGE был введен в SQL Server 2008, который произвел большую революцию в написании более простого и поддерживаемого кода на SQL. СЛИЯНИЕ
Оператор принимает две таблицы — исходную и целевую и сравнивает записи на основе ключевого столбца, часто столбца индекса, а затем выполняет над ним операцию. Как разработчик баз данных, я определенно посоветовал бы всем молодым программистам чаще использовать SQL-оператор MERGE при использовании сложных хранимых процедур в SQL.

Авеек — опытный инженер по данным и аналитике, в настоящее время работает в Дублине, Ирландия. Его основные технические интересы включают SQL Server, SSIS / ETL, SSAS, Python, инструменты для работы с большими данными, такие как Apache Spark, Kafka, и облачные технологии, такие как AWS / Amazon и Azure.

Он — плодовитый автор, опубликовавший более 100 статей в различных технических блогах, включая его собственный блог, и частый участник различных технических форумов.

В свободное время увлекается любительской фотографией, в основном уличной фотографией и натюрмортами.Некоторые взгляды на его работы можно найти в Instagram. Вы также можете найти его в LinkedIn

Просмотреть все сообщения Aveek Das

Последние сообщения Aveek Das (посмотреть все)

Введение в оператор слияния

Введение в оператор слияния и изменение данных SQL Server

Оператор MERGE используется для внесения изменений в одну таблицу на основе значений, сопоставленных с другими. Его можно использовать для объединения операций вставки, обновления и удаления в один оператор. В этой статье мы узнаем, как использовать оператор MERGE.Мы обсуждаем некоторые передовые практики, ограничения и подводим итоги на нескольких примерах.

Это пятая статья в серии статей. Вы можете начать с самого начала, прочитав Введение в операторы изменения данных SQL Server.

Все примеры для этого урока основаны на Microsoft SQL Server Management Studio и базе данных AdventureWorks2012. Вы можете начать использовать эти бесплатные инструменты, используя мое Руководство Начало работы с SQL Server

Прежде чем мы начнем

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

Базовая структура

Оператор MERGE объединяет операции INSERT, DELETE и UPDATE в одну таблицу. Как только вы поймете, как это работает, вы увидите, что это упрощает процедуру за счет использования всех трех операторов по отдельности для синхронизации данных.

Ниже приведен обобщенный формат оператора слияния.

 MERGE targetTable
Использование sourceTable
ON mergeCondition
При совпадении
ТОГДА updateStatement
КОГДА НЕ СООТВЕТСТВУЕТ ЦЕЛИ
ТОГДА insertStatement
КОГДА НЕ ПОДХОДИТ ПО ИСТОЧНИКУ
ЗАТЕМ deleteStatement 

Оператор слияния работает с двумя таблицами: sourceTable и targetTable. TargetTable — это таблица, которая должна быть изменена на основе данных, содержащихся в sourceTable.

Две таблицы сравниваются с помощью mergeCondition. Это условие определяет, как строки из sourceTable сопоставляются с targetTable. Если вы знакомы с INNER JOINS, вы можете думать об этом как об условии соединения, используемом для сопоставления строк.

Как правило, вам будет соответствовать уникальный идентификатор, например первичный ключ. Если исходной таблицей был NewProduct, а целевой ProductMaster и первичный ключ для обоих ProductID, то хорошим условием слияния было бы:

 NewProduct.ProductID = ProductMaster.ProductID 

Условие слияния приводит к одному из трех состояний: MATCHED , NOT MATCHED или NOT MATCHED BY SOURCE .

Условия слияния

Давайте рассмотрим, что означают различные условия:

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

NOT MATCHED — также известен как NOT MATCHED BY TARGET ; это строки из исходной таблицы, которые не соответствуют ни одной строке в целевой таблице. Эти строки представлены синей областью выше. В большинстве случаев это может использоваться для вывода о том, что исходные строки должны быть добавлены в targetTable.

NOT MATCHED BY SOURCE — это строки в целевой таблице, которые никогда не соответствовали исходной записи; это строки в оранжевой области.Если ваша цель — полностью синхронизировать данные targetTable с источником, вы воспользуетесь этим условием соответствия для УДАЛЕНИЯ строк.

Если у вас возникли проблемы с пониманием того, как это работает, рассмотрите условие слияния как условие соединения. СТРОКИ в зеленом разделе представляют строки, соответствующие условию слияния, строки в синем разделе — это те строки, которые находятся в SourceTable, но не в целевом объекте. Строки в оранжевом разделе — это те строки, которые находятся только в целевом объекте.

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

Давайте посмотрим на пример.

Пример MERGE

Предположим, наша цель — синхронизировать любые изменения, внесенные в esqlProductSource, с esqlProductTarget. Вот диаграмма этих двух таблиц:

Примечание. Для этого примера я запустил сценарии, о которых говорил во введении, для создания и заполнения двух таблиц: esqlProductSource и esqlProductTarget.

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

Думаю, если вы увидите, как мы делаем это по отдельности, то объединение в одну операцию станет более разумным.

Использование UPDATE для синхронизации изменений из одной таблицы в следующую

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

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

 ОБНОВЛЕНИЕ esqlProductTarget
НАБОР Имя = S.Имя,
       ProductNumber = S.ProductNumber,
       Цвет = S.Color
ОТ esqlProductTarget T
       ВНУТРЕННЕЕ СОЕДИНЕНИЕ esqlProductSource S
       ON S.ProductID = T.ProductID 

Этот оператор обновит столбец в esqlProductTarget соответствующими значениями столбца, найденными в esqlProductSource для соответствия идентификаторам продукта.

ВСТАВИТЬ строки, найденные в одной таблице, но не найденные в другой

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

 INSERT INTO esqlProductTarget (ProductID, Name, ProductNumber, Color)
ВЫБЕРИТЕ S.ProductID, S.Name, S.ProductNumber, S.Color
ОТ esqlProductSource S
ГДЕ НЕ СУЩЕСТВУЕТ (ВЫБЕРИТЕ T.ProductID
                  ОТ esqlProductTarget T
                  ГДЕ T.ProductID = S.ProductID) 

Примечание: я мог бы также использовать внешнее соединение, чтобы сделать то же самое. Если вам интересно, почему, прочтите эту статью.

Этот оператор вставит новую строку в esqlProductTarget из всех строк в esqlProductSource, которых нет в esqlProductTarget.

Удаление строк

Последнее действие по синхронизации, которое нам нужно выполнить, удаляет все строки в целевой таблице, которых нет в источнике SQL. Как и в случае с оператором вставки, мы будем использовать подзапрос. Но на этот раз мы определим строки в esqlProductTarget, которых нет в esqlProductSource. Вот оператор DELETE, который мы можем использовать:

 УДАЛИТЬ esqlProductTarget
ОТ esqlProductTarget T
ГДЕ НЕ СУЩЕСТВУЕТ (ВЫБЕРИТЕ S.Идантификационный номер продукта
                   ОТ esqlProductSource S
                   ГДЕ T.ProductID = S.ProductID) 

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

 MERGE esqlProductTarget T
ИСПОЛЬЗОВАНИЕ esqlProductSource S
ВКЛ (S.ProductID = T.ProductID)
При совпадении
     ТОГДА ОБНОВЛЕНИЕ
     НАБОР T.Name = S.Name,
            T.ProductNumber = S.ProductNumber,
            T.Color = S.Color
КОГДА НЕ СООТВЕТСТВУЕТ ЦЕЛИ
ЗАТЕМ ВСТАВЬТЕ (ProductID, Name, ProductNumber, Color)
     ЦЕННОСТИ (С.ProductID, S.Name, S.ProductNumber, S.Color)
КОГДА НЕ ПОДХОДИТ ПО ИСТОЧНИКУ
ТОГДА УДАЛИТЬ; 

Обратите внимание, что большая часть тяжелой работы выполняется условием слияния и его результатами. Вместо того, чтобы повторно настраивать сопоставление, как мы делали в операторе удаления, это делается один раз.

Снова сравните оператор Insert с оператором слияния выше.

 INSERT INTO esqlProductTarget (ProductID, Name, ProductNumber, Color)
ВЫБЕРИТЕ S.ProductID, S.Name, S.ProductNumber, S.Цвет
ОТ esqlProductSource S
ГДЕ НЕ СУЩЕСТВУЕТ (ВЫБЕРИТЕ T.ProductID
                  ОТ esqlProductTarget T
                  ГДЕ T.ProductID = S.ProductID) 

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

Регистрация изменений MERGE с использованием OUTPUT

Вы можете использовать предложение OUTPUT для регистрации любых изменений. В этом случае специальная переменная $ action может использоваться для регистрации действия слияния.Эта переменная примет одно из трех значений: «INSERT», «UPDATE» или «DELETE».

Мы продолжим использовать наш пример, но на этот раз мы внесем изменения в журнал и суммируем их.

 MERGE esqlProductTarget T
ИСПОЛЬЗОВАНИЕ esqlProductSource S
ВКЛ (S.ProductID = T.ProductID)
При совпадении
ТОГДА ОБНОВЛЕНИЕ
     НАБОР T.Name = S.Name,
            T.ProductNumber = S.ProductNumber,
            T.Color = S.Color
КОГДА НЕ СООТВЕТСТВУЕТ ЦЕЛИ
ЗАТЕМ ВСТАВЬТЕ (ProductID, Name, ProductNumber, Color)
     ЦЕННОСТИ (С.ProductID, S.Name, S.ProductNumber, S.Color)
КОГДА НЕ ПОДХОДИТ ПО ИСТОЧНИКУ
ТОГДА УДАЛИТЬ
ВЫВОДИТЕ S.ProductID, $ action в @MergeLog;

ВЫБЕРИТЕ MergeAction, count (*)
ОТ @MergeLog
ГРУППА ПО СЛИЯНИЮ 

Если описанное выше выполняется на свежих образцах данных, создается следующая сводка:

Оператор MERGE в SQL с объяснением

Предварительное условие — Оператор MERGE
Оператор MERGE в SQL, как обсуждалось ранее в предыдущем посте, представляет собой комбинацию трех операторов INSERT, DELETE и UPDATE. Таким образом, если есть исходная таблица и целевая таблица , которые должны быть объединены, то с помощью оператора MERGE все три операции (INSERT, UPDATE, DELETE) могут быть выполнены одновременно.

Простой пример поясняет использование оператора MERGE.
Пример:

Предположим, есть две таблицы:

1. PRODUCT_LIST — таблица, содержащая текущие сведения о доступных продуктах с полями P_ID, P_NAME и P_PRICE, соответствующими идентификатору, названию и цене каждого продукта.
2. UPDATED_LIST — таблица, содержащая новые сведения о доступных продуктах с полями P_ID, P_NAME и P_PRICE, соответствующими идентификатору, названию и цене каждого продукта.

Задача состоит в том, чтобы обновить детали продуктов в PRODUCT_LIST согласно UPDATED_LIST.

Решение
Теперь, чтобы лучше объяснить этот пример, давайте разделим его на шаги.

• Шаг 1: Распознать TARGET и таблицу SOURCE
  Итак, в этом примере, поскольку ему предлагается обновить продукты в PRODUCT_LIST в соответствии с UPDATED_LIST, поэтому PRODUCT_LIST будет действовать как TARGET, а UPDATED_LIST будет действовать как ИСТОЧНИК таблица.

• Шаг 2: Распознайте операции, которые необходимо выполнить.
  Теперь, как можно видеть, есть три несоответствия между таблицей TARGET и SOURCE, а именно:
  1. Стоимость КОФЕ в TARGET составляет 15,00, а в SOURCE — 25,00

           СПИСОК ПРОДУКТОВ
     102 КОФЕ 15.00
     
           UPDATED_LIST
     102 КОФЕ 25.00
      

  2. В SOURCE нет продукта BISCUIT, но он находится в TARGET.

           СПИСОК ПРОДУКТОВ
     103 ПЕЧЕНЬЕ 20.00
      

  3. В TARGET нет продукта CHIPS, но он находится в SOURCE.

           UPDATED_LIST
     104 ЧИПСА 22.00
      

  Следовательно, в TARGET необходимо выполнить три операции в соответствии с указанными выше несоответствиями. Их:

  1. операция UDPATE

     102 КОФЕ 25.00
      

  2. операция УДАЛИТЬ

     103 ПЕЧЕНЬЕ 20.00
      

  3. операция INSERT

     104 ЧИПСА 22.00
      

• Шаг 3. Напишите запрос SQL.
  
  

  Примечание. Синтаксис оператора MERGE см. В этом посте.

  SQL-запрос для выполнения вышеупомянутых операций с помощью оператора MERGE :

  / * Выбор цели и Источник * / ОБЪЕДИНЕНИЕ СПИСОК ПРОДУКТОВ КАК ЦЕЛЬ ИСПОЛЬЗОВАНИЕ UPDATE_LIST AS ИСТОЧНИК / * 1.Выполнение операции UPDATE * / / * Если P_ID равен то же самое, чек для сдача дюйм P_NAME или P_PRICE * / ON (TARGET. P_ID = SOURCE.P_ID) КОГДА СОГЛАСОВАНО И ЦЕЛЬ.P_NAME <> SOURCE.P_NAME ИЛИ TARGET.P_PRICE <> SOURCE.P_PRICE / * Обновление записей в TARGET * / ТОГДА ОБНОВЛЕНИЕ НАБОР TARGET.P_NAME = SOURCE.P_NAME, TARGET.P_PRICE = ИСТОЧНИК.P_PRICE / * 2. Выполнение операции INSERT * / / * Когда нет записей совпадают с TARGET table Затем вставить записи в цель таблица * / КОГДА НЕ СОГЛАСОВАНО ПО ЦЕЛЬ ТОГДА ВСТАВИТЬ (P_ID, P_NAME, P_PRICE) ЗНАЧЕНИЯ (ИСТОЧНИК. P_ID, SOURCE.P_NAME, SOURCE.P_PRICE) / * 3. Выполнение операции удаления DELETE * / / * Когда нет записей совпадают с ИСТОЧНИК таблица Затем удалить записи из цель таблица * / КОГДА НЕ СОГЛАСОВАНО ПО ИСТОЧНИК ЗАТЕМ УДАЛИТЬ / * КОНЕЦ ИЗ ОБЪЕДИНЕНИЕ * /

Выход:

       СПИСОК ПРОДУКТОВ
P_ID P_NAME P_PRICE
101 ЧАЙ 10. 00
102 КОФЕ 25.00
104 ЧИПСА 22.00 

Итак, таким образом мы можем выполнять все эти три основных оператора SQL вместе с помощью оператора MERGE.

Примечание: В синтаксисе MERGE можно использовать любое имя, кроме цели и источника. Они используются только для лучшего объяснения.

Автор статьи Димпи Варшни . Если вам нравится GeeksforGeeks, и вы хотели бы внести свой вклад, вы также можете написать статью, используя вклад.geeksforgeeks.org или отправьте свою статью по адресу [email protected]. Смотрите, как ваша статья появляется на главной странице GeeksforGeeks, и помогайте другим гикам.

Пожалуйста, напишите комментарий, если вы обнаружите что-то неправильное, или если вы хотите поделиться дополнительной информацией по теме, обсужденной выше.

Вниманию читателя! Не прекращайте учиться сейчас. Получите все важные концепции теории CS для собеседований SDE с помощью курса CS Theory Course по приемлемой для студентов цене и будьте готовы к работе в отрасли.

Оператор MERGE в SQL Server 2008

Начиная с SQL Server 2008, вы можете использовать оператор MERGE для изменения данных в целевой таблице на основе данных в исходной таблице. Оператор соединяет цель с источником с помощью столбца, общего для обеих таблиц, например первичного ключа. Затем вы можете вставлять, изменять или удалять данные из целевой таблицы — все в одном операторе — в зависимости от того, как строки совпадают в результате соединения.

Оператор MERGE поддерживает несколько предложений, которые упрощают различные типы модификации данных.В этой статье я объясню каждый из этих пунктов и приведу примеры, демонстрирующие, как они работают. Я создал примеры на локальном экземпляре SQL Server 2008. Чтобы опробовать их, вам нужно сначала запустить следующий сценарий для создания и заполнения таблиц, используемых в примерах:

Как видите, сценарий создает и заполняет таблицы BookInventory и BookOrder. Таблица BookInventory представляет книги, которые есть или были доступны в магазине вымышленных книг. Если значение количества для книги равно 0, то книга распродана.

В таблице BookOrder показаны книги, заказ на которые был размещен и доставлен. Если значение количества для книги, указанной в этой таблице, равно 0, значит, книга была запрошена, но не включена в поставку. Суммированные значения количества в обеих таблицах представляют собой текущие запасы компании.

ПРИМЕЧАНИЕ. Я создал таблицы BookInventory и BookOrder в образце базы данных AdventureWorks2008.Вы можете создавать таблицы в любой пользовательской базе данных SQL Server 2008. Обязательно измените оператор USE в приведенном выше сценарии, чтобы он точно отражал целевую базу данных.

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

Например, если значение BookID в таблице BookInventory совпадает со значением BookID в таблице BookOrder, строки считаются совпадающими, независимо от других значений в совпадающих строках.Когда строки совпадают, вы можете использовать предложение WHEN MATCHED для изменения данных в целевой таблице. Давайте посмотрим на пример, чтобы продемонстрировать, как это работает.

В следующем операторе MERGE я присоединяю таблицу BookInventory (цель) к таблице BookOrder (источник), а затем использую предложение WHEN MATCHED для обновления столбца Quantity в целевой таблице:

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

Следующая строка в операторе — это предложение USING, которое состоит из ключевого слова USING, за которым следует исходная таблица (опять же, при необходимости). Я также присвоил этой таблице псевдоним (бо). Затем я использовал предложение ON для объединения двух таблиц на основе значения TitleID в каждой таблице (bi.TitleID = bo.TitleID).

После того, как я указал целевую и исходную таблицы, а также условие соединения, я добавил предложение WHEN MATCHED. Предложение включает ключевые слова WHEN MATCHED, за которыми следует ключевое слово THEN, затем ключевое слово UPDATE и, наконец, подпункт SET.В этом случае выражение SET указывает, что новое значение Quantity в целевой таблице должно равняться сумме значений Quantity как из целевой, так и из исходной таблиц (bi. Quantity = bi.Quantity + bo.Quantity). Таким образом, новое значение количества в целевой таблице будет отражать точное количество книг, имеющихся в наличии для продажи, то есть количество книг, которые были в наличии, плюс количество, поступившее с последним заказом.

Вот и все, что нужно для создания базового оператора MATCH.Я также включил оператор SELECT, чтобы мы могли просматривать содержимое целевой таблицы. Следующие результаты отражают новые значения в столбце Quantity, как они появляются в таблице BookInventory после ее обновления с помощью оператора MERGE:

Как показывают результаты запроса, несколько строк были обновлены, чтобы отразить общий объем запасов на основе сумм как в целевой, так и в исходной таблицах. Например, строка в таблице BookInventory со значением TitleID, равным 1 ( The Catcher in the Rye ), первоначально показывала три книги в наличии. Однако, согласно таблице BookOrder, было заказано еще шесть книг, в результате чего компания получила девять книг. Как и следовало ожидать, значение Quantity в таблице BookInventory теперь равно 9.

Возможно, вы заметили, что книга со значением BookID 3 ( The Great Gatsby ) изначально имела значение Quantity равное 0 как в исходной, так и в целевой таблицах.Предположим, вы хотите удалить из таблицы BookInventory любую книгу, значение Quantity которой равно 0 как в целевой, так и в исходной таблицах. Вы можете легко удалить такие строки, добавив второе предложение WHEN MATCHED к вашему оператору MATCH, как показано в следующем примере:

Обратите внимание, что новое предложение WHEN MATCHED включает конкретное условие поиска после ключевого слова AND (bi.Quantity + bo.Quantity = 0). В результате, когда строки из двух таблиц совпадают с и , два столбца Quantity из сопоставленных строк в сумме дают 0, строка будет удалена, что обозначено ключевым словом DELETE.Теперь оператор SELECT возвращает следующие результаты:

Как видите, книга The Great Gatsby удалена из инвентаря. Однако следует отметить, что оператор MERGE может включать не более двух предложений WHEN MATCHED. Каждый раз, когда вы включаете два из этих предложений, первое предложение должно включать ключевое слово AND, за которым следует условие поиска, как я сделал здесь. Второе предложение WHEN MATCHED применяется только в том случае, если первое — нет.

ПРИМЕЧАНИЕ: Примеры в этой статье не зависят друг от друга. То есть для каждого запускаемого примера вы должны сначала повторно запустить сценарий создания таблицы, если хотите, чтобы ваши результаты соответствовали показанным здесь.

Следующий пункт в операторе MERGE, который мы рассмотрим, — WHEN NOT MATCHED [BY TARGET]. (Ключевые слова BY TARGET являются необязательными.) Вы должны использовать это предложение для вставки новых строк в целевую таблицу. Строки, которые вы вставляете в таблицу, — это те строки в исходной таблице, для которых нет совпадающих строк в целевой. Например, таблица BookOrder содержит строку для Унесенные ветром . Однако таблица BookInventory не содержит этой книги. В следующем примере показано, как включить предложение WHEN NOT MATCHED в ваш оператор MERGE, который добавляет Gone with the Wind в вашу целевую таблицу:

Как видно из инструкции, я сначала указываю ключевые слова WHEN NOT MATCHED BY TARGET, за которыми следует ключевое слово THEN и, наконец, за ними следует предложение INSERT.Предложение INSERT состоит из двух частей: подпункта INSERT и подпункта VALUES. Вы указываете целевые столбцы в подпункте INSERT, а исходные значения — в предложении VALUES. Обратите внимание, что для подпункта VALUES я должен уточнять имена столбцов с помощью псевдонима таблицы. Оператор SELECT теперь возвращает следующие результаты:

В таблицу BookInventory были добавлены две новые строки: одна для Унесенных ветром и одна для Age of Innocence. Поскольку книги существовали в исходной таблице, но не в целевой таблице, они были вставлены в таблицу BookInventory.

Как вы помните из обсуждения предложения WHEN MATCHED, вы можете использовать это предложение для удаления строк из целевой таблицы. Однако вы можете удалить строку, соответствующую строке в исходной таблице. Но предположим, что вы хотите удалить строку из целевой таблицы, которая не соответствует строке в исходной таблице.

Например, одна из строк, изначально вставленных в таблицу BookInventory, предназначена для книги Catch 22 .Значение Quantity для этой книги никогда не обновлялось, потому что на книгу не был размещен заказ, то есть книга никогда не добавлялась в таблицу BookOrder. Поскольку на складе нет копий этой книги, вы можете удалить эту книгу из целевой таблицы. Чтобы удалить строку, которая не соответствует строке в исходной таблице, необходимо использовать предложение WHEN NOT MATCHED BY SOURCE.

ПРИМЕЧАНИЕ. Как и предложение WHEN MATCHED, вы можете включить до двух предложений WHEN NOT MATCHED BY SOURCE в свой оператор MERGE. Если вы включаете два, первое предложение должно включать ключевое слово AND, за которым следует условие поиска.

Следующий пример включает предложение WHEN NOT MATCHED BY SOURCE, которое указывает, что любые строки с количеством 0, которые не соответствуют источнику, должны быть удалены:

После того, как я указал ключевые слова WHEN NOT MATCHED BY SOURCE, я указал AND, за которым следует условие поиска (bi.Количество = 0). Затем я добавил ключевое слово THEN, а затем ключевое слово DELETE. Результаты, возвращаемые оператором SELECT, показаны в следующей таблице:

Как видите, таблица BookInventory больше не включает строку для Catch 22 . И поскольку три предложения MERGE использовались вместе, BookInventory теперь отражает точное количество книг, которые в настоящее время есть в наличии, и не включены книги, которых нет в наличии.

Когда был выпущен SQL Server 2005, он включал поддержку предложения OUTPUT в нескольких операторах языка модификации данных (DML). Предложение OUTPUT также доступно в операторе MERGE. Предложение OUTPUT возвращает копию данных, которые вы вставили в таблицы или удалили из них.При использовании с оператором MERGE это предложение предоставляет вам мощный инструмент для сбора измененных данных для архивирования, обмена сообщениями или приложений.

ПРИМЕЧАНИЕ: Чтобы узнать больше о предложении OUTPUT, см. Статью «Реализация предложения OUTPUT в SQL Server 2008» (http://www.simple-talk.com/sql/learn-sql-server/implementing-the -выход-предложение-в-sql-server-2008 /).

В следующем примере я использую предложение OUTPUT для передачи выведенных данных в переменную с именем @MergeOutput:

Обратите внимание, что я сначала объявляю табличную переменную @MergeOutput. В переменную я включаю столбец для типа действия плюс три дополнительных набора столбцов.Каждый набор соответствует столбцам в целевой таблице и включает столбец, в котором показаны удаленные данные, и столбец, в котором показаны вставленные данные. Например, столбцы DelTitleID и InsTitleID соответствуют удаленным и вставленным значениям, соответственно, в целевой таблице.

Предложение OUTPUT сначала определяет встроенную переменную $ action, которая возвращает одно из трех значений nvarchar (10): INSERT, UPDATE или DELETE. Переменная доступна только для оператора MERGE. Я следую за переменной с набором префиксов столбцов (DELETED и INSERTED) для каждого столбца в целевой таблице.За префиксом столбца следует имя столбца, к которому они относятся. Например, я включаю DELETED.TitleID и INSERTED.TitleID для столбца TitleID в целевой таблице. После указания префиксов столбцов я включаю подпункт INTO, в котором указывается, что выводимые значения должны быть сохранены в переменной @MergeOutput.

После предложения OUTPUT, которое является последним предложением в моем операторе MERGE, я добавил оператор SELECT для получения обновленного содержимого таблицы BookInventory, как я делал в предыдущих примерах.Оператор SELECT возвращает следующие результаты:

Обратите внимание, что я также включил второй оператор SELECT в свой пример выше.Этот оператор извлекает содержимое переменной @MergeOutput. Результаты запроса показаны в следующей таблице:

Результаты показывают все действия, которые были выполнены в целевой таблице. Например, строка для the Great Gatsby указывает, что эта строка была удалена из таблицы BookInventory. Столбцы DelTitleID, DelTitle и DelQuantity показывают значения, которые были удалены. Однако все столбцы InsTitleID, InsTitle и InsQuantity показывают нулевые значения. Это потому, что данные для этой строки не были вставлены в целевую таблицу. Если строка была вставлена, столбцы InsTitleID, InsTitle и InsQuantity будут отображать вставленные значения, но столбцы DelTitleID, DelTitle и DelQuantity будут отображать нулевые значения, поскольку при выполнении вставки ничего не удаляется.Все обновленные строки будут иметь значения во всех столбцах.

Как видите, при использовании предложения OUTPUT в сочетании с другими тремя предложениями оператора MERGE — WHEN MATCHED, WHEN NOT MATCHED [BY TARGET] и WHEN NOT MATCHED BY SOURCE — вы можете выполнить несколько операций DML и проверить свои данные. модификации легко и качественно. Оператор MERGE может упростить ваш код, повысить производительность и сократить усилия по разработке. Дополнительные сведения об операторе MERGE см. В разделе «Вставка, обновление и удаление данных с помощью MERGE» электронной документации по SQL Server 2008.

Пример слияния SQL | Оператор слияния в учебнике SQL

ОПЕРАТОР СЛИЯНИЯ SQL — это комбинация операторов INSERT, UPDATE и DELETE. Оператор слияния может выполнять все эти операции в нашей основной целевой таблице, если указана исходная таблица. MERGE очень полезно, когда дело доходит до загрузки таблиц хранилища данных, которые могут быть очень большими и требовать выполнения определенных действий, когда строки присутствуют или отсутствуют.

Оператор слияния SQL

См. Следующий синтаксис.

 MERGE  [AS TARGET]
ИСПОЛЬЗОВАНИЕ  [КАК ИСТОЧНИК]
ВКЛ <условие_поиска>
[ПРИ СОГЛАСОВАНИИ
ТОГДА ]
[КОГДА НЕ СОГЛАСОВАНЫ [ПО ЦЕЛИ]
ТОГДА ]
[ЕСЛИ ИСТОЧНИК НЕ ПОДХОДИТ
ТОГДА ];
 

# Как использовать SQL MERGE STATEMENT

1. Определите целевую таблицу, которая будет использоваться в этой логике.
2. Следующим шагом является определение исходной таблицы, которую мы можем использовать в логике.
3. Следующим шагом является определение соответствующих условий поиска в предложении ON для соответствия строкам.
4. Реализовать логику, когда записи совпадают или не совпадают между целью и источником.
5. Для каждого из этих сравнений условия записывают логику, и при сопоставлении обычно используется условие обновления, а при отсутствии сопоставления используется оператор вставки или удаления.

Давайте проясним это на примере:

Рассмотрим таблицу Продукты : (Это будет считаться целевой таблицей).

Updated_Products : (Это будет считаться таблицей ИСТОЧНИКОВ).

#QUERY

 TARGET
ИСПОЛЬЗОВАНИЕ UPDATED_PRODUCTS КАК ИСТОЧНИК
ВКЛЮЧЕНО (TARGET.ID = SOURCE.ID)
THEN MATCHED AND TARGET.NAME SOUCE.NAME
ИЛИ ЦЕЛЕВАЯ ЦЕНА ИСТОЧНИК.ЦЕНА ТОГДА
ОБНОВЛЕНИЕ УСТАНОВИТЬ TARGET.NAME = SOURCE.NAME,
TARGET.PRICE = SOURCE.PRICE
КОГДА НЕ СОГЛАСОВАНА ЦЕЛЬ ТО
ВСТАВИТЬ (ID, НАЗВАНИЕ, ЦЕНА)
ЦЕННОСТИ (ИСТОЧНИК.ID, SOURCE.NAME, SOURCE.PRICE)
КОГДА НЕ ПОДХОДИТ ПО ИСТОЧНИКУ ТО
УДАЛИТЬ;
 

#Output

Итак, после выполнения вышеуказанного запроса таблица Products будет заменена таблицей Updated_products.

Вы можете увидеть таблицу ниже.

ID	НАИМЕНОВАНИЕ	ЦЕНА
101	Чай	5. 00
201		Biscuits00
301	Кофе	25,00

Таким образом, мы можем выполнять все три операции вместе, используя предложение MERGE.

ПРИМЕЧАНИЕ:

Мы можем использовать любое имя, кроме источника и цели. Мы использовали эти имена, чтобы дать вам лучшее объяснение.

# Некоторые основные ключевые моменты

1. Для инструкции MERGE SQL требуется точка с запятой (;) в качестве признака конца инструкции. В противном случае будет вызвана ошибка 10713.
2. По крайней мере одно из трех предложений MATCHED должно быть указано, когда мы используем оператор MERGE.
3. Пользователь, использующий оператор MERGE, должен иметь разрешение SELECT для таблицы SOURCE и разрешения INSERT, UPDATE и DELETE для таблицы TARGET.
4. При вставке, удалении или обновлении с помощью оператора слияния в SQL Server запускаются все соответствующие триггеры AFTER, определенные в этой целевой таблице, но не гарантируется, какое действие запускает триггеры первым или последним.

Наконец, пример слияния SQL завершен.

Пожалуйста, прекратите использовать этот анти-шаблон UPSERT

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

 ЕСЛИ СУЩЕСТВУЕТ (ВЫБЕРИТЕ 1 ИЗ dbo.t WHERE [key] = @key)
НАЧИНАТЬ
  ОБНОВЛЕНИЕ dbo.t SET val = @val WHERE [ключ] = @key;
КОНЕЦ
ЕЩЕ
НАЧИНАТЬ
  ВСТАВИТЬ dbo.t ([ключ], val) ЗНАЧЕНИЯ (@key, @val);
КОНЕЦ 

Это выглядит довольно логичным потоком, который отражает то, как мы думаем об этом в реальной жизни:

• Строка для этого ключа уже существует?
  • ДА : ОК, обновите эту строку.
  • НЕТ : ОК, затем добавьте.

Но это расточительно.

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

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

Что еще более важно, производительность в сторону, если вы одновременно не используете явную транзакцию и не повысите уровень изоляции, несколько вещей могут пойти не так, когда строка еще не существует:

• Если ключ существует и два сеанса пытаются обновить одновременно, они оба успешно обновят (один «выиграет»; «проигравший» последует с изменением, которое закрепится, что приведет к «потерянному обновлению»). Это не проблема сама по себе, и именно так мы, , должны, , ожидать, что система с параллелизмом будет работать.Пол Уайт говорит здесь более подробно о внутренней механике, а Мартин Смит говорит здесь о некоторых других нюансах.
• Если ключ не существует, но оба сеанса проходят проверку существования одинаково, может произойти что угодно, когда они оба попытаются вставить:
  • тупик из-за несовместимых блокировок;
  • вызывает ключевые ошибки нарушения , которых не должно было случиться; или же,
  • вставьте повторяющиеся значения ключа , если этот столбец не ограничен должным образом.

Последний самый худший, ИМХО, потому что он потенциально портит данные . Взаимоблокировки и исключения можно легко обрабатывать с помощью таких вещей, как обработка ошибок, XACT_ABORT и логика повтора, в зависимости от того, как часто вы ожидаете столкновений. Но если вас убаюкивает чувство безопасности, что проверка IF EXISTS защищает вас от дубликатов (или ключевых нарушений), это ожидает своего часа сюрприз. Если вы ожидаете, что столбец будет действовать как ключ, сделайте его официальным и добавьте ограничение.

«Многие говорят…»

Дэн Гусман говорил об условиях гонки более десяти лет назад в Условии гонки Условное ВСТАВИТЬ / ОБНОВЛЕНИЕ, а затем в Условии гонки «UPSERT» с MERGE.

Майкл Сварт также затронул эту тему несколько лет назад в своей книге «Разрушение мифов: решения для одновременного обновления / вставки», включая тот факт, что при сохранении исходной логики и только повышении уровня изоляции ключевые нарушения менялись на взаимоблокировки. Позже он проверил свой энтузиазм по поводу MERGE в Be Careful with the Merge Statement.Убедитесь, что вы также прочитали все комментарии к обоим сообщениям.

Решение

За свою карьеру я исправил множество взаимоблокировок, просто приспособившись к следующему шаблону (отказаться от избыточной проверки, заключить последовательность в транзакцию и защитить первый доступ к таблице соответствующей блокировкой):

 НАЧАТЬ СДЕЛКУ;

ОБНОВЛЕНИЕ dbo.t С ПОМОЩЬЮ (UPDLOCK, SERIALIZABLE) SET val = @val WHERE [key] = @key;

ЕСЛИ @@ ROWCOUNT = 0
НАЧИНАТЬ
  ВСТАВИТЬ dbo.t ([ключ], val) ЗНАЧЕНИЯ (@key, @val);
КОНЕЦ

ЗАВЕРШИТЬ СДЕЛКУ; 

Зачем нужны две подсказки? Разве UPDLOCK недостаточно?

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

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

В этом случае для одного пути выполняется только одна индексная операция.

Но опять же, в сторону производительности:

• Если ключ существует и два сеанса пытаются обновить его одновременно, они по очереди обновят строку , как и раньше.
• Если ключ не существует, один сеанс «выиграет» и вставит строку .Другой должен будет подождать , пока блокировки не будут сняты, чтобы даже проверить наличие, и будет вынужден обновить.

В обоих случаях писатель, выигравший гонку, теряет свои данные из-за того, что «проигравший» обновил после него.

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

Но что делать, если обновление менее вероятно?

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

 НАЧАТЬ СДЕЛКУ;

ВСТАВИТЬ dbo. t ([ключ], val)
  ВЫБРАТЬ @key, @val
  ГДЕ НЕ СУЩЕСТВУЕТ
  (
    ВЫБРАТЬ 1 ИЗ dbo.t С ПОМОЩЬЮ (ОБНОВЛЕНИЕ, СЕРИАЛИЗАЦИЯ)
      ГДЕ [ключ] = @ ключ
  );

ЕСЛИ @@ ROWCOUNT = 0
НАЧИНАТЬ
  ОБНОВЛЕНИЕ dbo.t SET val = @val WHERE [ключ] = @key;
КОНЕЦ

ЗАВЕРШИТЬ СДЕЛКУ; 

Существует также подход «просто сделай это», когда вы вслепую вставляете и позволяете коллизиям вызывать исключения для вызывающего:

 НАЧАТЬ СДЕЛКУ;

НАЧАТЬ ПОПРОБОВАТЬ
  ВСТАВИТЬ dbo.t ([ключ], val) ЗНАЧЕНИЯ (@key, @val);
КОНЕЦ ПОПЫТКИ
НАЧАТЬ ЛОВ
  ОБНОВЛЕНИЕ dbo.t SET val = @val WHERE [ключ] = @key;
КОНЕЦ ЗАХВАТ

ЗАВЕРШИТЬ СДЕЛКУ; 

Стоимость этих исключений часто превышает стоимость проверки в первую очередь; вам придется попробовать это с приблизительно точным предположением о частоте попаданий / промахов. Я писал об этом здесь и здесь.

А как насчет добавления нескольких строк?

Вышеупомянутое касается решений вставки / обновления одиночных элементов, но Джастин Пилинг спросил, что делать, если вы обрабатываете несколько строк, не зная, какие из них уже существуют?

Предполагая, что вы отправляете набор строк с использованием чего-то вроде параметра с табличным значением, вы должны обновить его с помощью соединения, а затем вставить с помощью NOT EXISTS, но шаблон все равно будет эквивалентен первому подходу, описанному выше:

 СОЗДАТЬ ПРОЦЕДУРУ dbo. UpsertTheThings
    @tvp dbo.TableType ТОЛЬКО ДЛЯ ЧТЕНИЯ
В КАЧЕСТВЕ
НАЧИНАТЬ
  УСТАНОВИТЬ NOCOUNT ON;

  НАЧАТЬ СДЕЛКУ;

  ОБНОВИТЬ t С ПОМОЩЬЮ (ОБНОВЛЕНИЕ, СЕРИАЛИЗАЦИЯ)
    НАБОР val = tvp.val
  ОТ dbo.t AS t
  ВНУТРЕННИЙ ПРИСОЕДИНЕНИЕ @tvp AS tvp
    ВКЛ t. [Ключ] = твп. [Ключ];

  INSERT dbo.t ([ключ], val)
    ВЫБЕРИТЕ [ключ], val FROM @tvp AS tvp
    ГДЕ НЕ СУЩЕСТВУЕТ (ВЫБЕРИТЕ 1 ИЗ dbo.t ГДЕ [ключ] = tvp. [Ключ]);

  ЗАВЕРШИТЬ СДЕЛКУ;
КОНЕЦ 

Если вы объединяете несколько строк каким-либо иным способом, кроме TVP (XML, список с разделителями-запятыми, voodoo), сначала поместите их в форму таблицы и присоединитесь к тому, что есть.Будьте осторожны, не оптимизируйте сначала вставки в этом сценарии, иначе вы потенциально можете обновить некоторые строки дважды.

Заключение

Эти паттерны апсерта превосходят те, которые я вижу слишком часто, и я надеюсь, что вы начнете их использовать. Я буду указывать на этот пост каждый раз, когда замечаю паттерн IF EXISTS в дикой природе. И, привет, еще один привет Полу Уайту (sql.kiwi | @SQK_Kiwi), потому что он так превосходно умеет делать сложные концепции простыми для понимания и, в свою очередь, объяснять.

И если вы чувствуете, что должны использовать MERGE , пожалуйста, не @ me; либо у вас есть веская причина (возможно, вам нужна какая-то непонятная функция только для MERGE ), либо вы не отнеслись к приведенным выше ссылкам серьезно.

Sunday T-SQL Tip: слейтесь с CTE в качестве цели

Если вы какое-то время работали с Microsoft SQL Server 2008, вы должны знать об операторе Merge. Этот оператор не только позволяет вам вставлять / обновлять / удалять данные как часть одного оператора (что помогает с блокировкой и производительностью), он также дает вам возможность перехватывать значения столбцов из исходного набора строк — то, что вы не можете сделать с обычное предложение OUTPUT операторов вставки, обновления и удаления.

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

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

В этом примере заказы сортируются по идентификатору и разделяются по клиентам, поэтому больший идентификатор означает более свежие заказы. Как видите — у вас 100 клиентов с 15 заказами в кеше.

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

Как видите, в этом примере мы добавили 10 заказов на одного клиента для 21 старого клиента (CustomerIds от 80 до 100), а также добавили 10 новых клиентов (CustomerIds от 101 до 110).

В конце мы хотим обновить кеш для существующих клиентов (удалить первые 10 старых заказов) и добавить в кеш новых клиентов. Очевидно, мы не хотим трогать клиентов, которые не отправляли никаких заказов в течение дня.

Оператор слияния здесь отлично подойдет. Хотя, если мы будем использовать таблицу данных в качестве цели, нам будет сложно отличить клиентов, которые не предоставили никаких данных. К счастью, мы можем установить CTE, который отфильтровывает клиентов, у которых сегодня нет заказов, и использует его в качестве цели. Давайте посмотрим:

Итак, первый CTE — SourceData — делает свое дело — он отфильтровывает всех, у кого нет новых заказов. Это будет наша Target . Теперь давайте подготовим Source — первое, что нам нужно сделать, это объединить данные из кеша с новыми данными — это делает MergedData CTE.В результате этого CTE у нас будут объединены все старые и новые заказы для клиентов, которые отправляют заказы сегодня. Далее — нам нужно определить последние 15 заказов — в основном давайте отсортируем MergedData (используйте ROW_NUMBER ()) на основе идентификатора в порядке убывания. Вот SortedData CTE. И теперь мы можем использовать первые 15 строк для каждого клиента из этого CTE в качестве Source .

Уловка заключается в том, что делать дальше — если в SourceData есть порядок, которого нет в Source (первые 15 из SortedData ) — это означает, что порядок устарел и нам нужно удалить его из кеша.« Когда не соответствует исходному » делает это. Если заказ находится в Source , но не в кэше — нам нужно вставить его («, когда не соответствует Target »).