深入理解SQL中的MERGE语句

数据库操作中,数据同步是一个常见需求。为了简化插入、更新和删除操作,SQL提供了MERGE语句,它能够将这些操作合并为一条语句执行。本文将探讨如何使用MERGE语句,讨论一些最佳实践、局限性,并以几个示例作为结尾。

这是一系列文章中的第五篇。可以从《SQLServer数据修改语句入门》开始阅读。本文的所有示例都基于Microsoft SQL Server Management Studio和AdventureWorks2012数据库。可以通过指南《开始使用SQL Server》来开始使用这些免费工具。

尽管本文使用AdventureWorks数据库作为示例,但决定在数据库中创建几个示例表,以更好地说明所涵盖的概念。可以在这里找到需要运行的脚本。注意有一个特殊部分是关于MERGE的。

基本结构

MERGE语句将INSERT、DELETE和UPDATE操作合并到一个表中。一旦理解了它的工作原理,会发现它简化了使用这三个语句分别同步数据的过程。

下面是一个MERGE语句的通用格式:

MERGE targetTable USING sourceTable ON mergeCondition WHEN MATCHED THEN updateStatement WHEN NOT MATCHED BY TARGET THEN insertStatement WHEN NOT MATCHED BY SOURCE THEN deleteStatement

MERGE语句使用两个表:sourceTable和targetTable。targetTable是要根据sourceTable中包含的数据进行修改的表。

这两个表通过mergeCondition进行比较。这个条件指定了如何将sourceTable中的行与targetTable中的行进行匹配。如果熟悉INNER JOINS,可以将其视为用于匹配行的连接条件。

通常,会匹配一个唯一标识符,例如主键。如果源表是NewProduct,目标表是ProductMaster,并且两个表的主键都是ProductID,那么一个好的合并条件可能是:

NewProduct.ProductID = ProductMaster.ProductID

合并条件会导致三种状态之一:

  • MATCHED - 这些是满足匹配条件的行。它们同时存在于源表和目标表中。在图表中,它们被显示为绿色。当在合并语句中使用这个条件时,很可能正在使用sourceTable列值更新目标行的列。
  • NOT MATCHED - 这也被称为NOT MATCHED BY TARGET;这些是源表中的行,它们没有与目标表中的任何行匹配。这些行在上面的图表中由蓝色区域表示。在大多数情况下,这可以用来推断源行应该被添加到targetTable中。
  • NOT MATCHED BY SOURCE - 这些是目标表中的行,它们从未与源记录匹配;这些是橙色区域中的行。如果目标是完全同步targetTable数据与source,那么会使用这个匹配条件来DELETE行。

如果理解有困难,可以考虑合并条件就像连接条件一样。绿色部分的行代表满足合并条件的行,蓝色部分的行是源表中的行,但不在目标中。橙色部分的行是仅在目标中找到的行。

考虑到这些匹配场景,可以轻松地将添加、删除和更新活动合并到一个语句中,以同步两个表之间的更改。

示例

假设目标是同步对esqlProductSource和esqlProductTarget所做的任何更改。这里是这两个表的图表:

注意:为了这个示例,运行了在介绍中谈到的脚本,创建并填充了两个表:esqlProductSource和esqlProductTarget。

在构建MERGE语句之前,让看看如何使用UPDATE、INSERT和DELETE语句来修改、添加和删除目标表中的行。

认为一旦看到如何单独完成的,那么看到它们合并成一个单一操作就更有感觉了。

要使用产品源中的更改值更新目标表,可以使用UPDATE语句。鉴于ProductID是两个表的主键,它成为最好的选择,用于匹配表之间的行。

如果想使用源列更新目标表中的列值,可以使用以下更新语句:

UPDATE esqlProductTarget SET Name = S.Name, ProductNumber = S.ProductNumber, Color = S.Color FROM esqlProductTarget T INNER JOIN esqlProductSource S ON S.ProductID = T.ProductID

这个语句将使用在esqlProductSource中找到的相应列值更新esqlProductTarget中的列。

现在让看看如何识别源表中需要插入到产品目标中的行。为此,可以使用子查询来找到源表中不在目标中的行。

INSERT INTO esqlProductTarget (ProductID, Name, ProductNumber, Color) SELECT S.ProductID, S.Name, S.ProductNumber, S.Color FROM esqlProductSource S WHERE NOT EXISTS (SELECT T.ProductID FROM esqlProductTarget T WHERE T.ProductID = S.ProductID)

这个语句将从所有在esqlProductSource中但不在esqlProductTarget中的行中,将新行插入到esqlProductTarget中。

需要做的最后一个同步活动是删除目标表中不在SQL源中的任何行。就像使用插入语句一样,将使用子查询。但这次将识别在esqlProductTarget中但不在esqlProductSource中的行。这是可以使用的DELETE语句:

DELETE esqlProductTarget FROM esqlProductTarget T WHERE NOT EXISTS (SELECT S.ProductID FROM esqlProductSource S WHERE T.ProductID = S.ProductID)

现在已经看到了如何分别进行各种操作,让看看它们是如何在合并语句中结合在一起的。

MERGE esqlProductTarget T USING esqlProductSource S ON (S.ProductID = T.ProductID) WHEN MATCHED THEN UPDATE SET T.Name = S.Name, T.ProductNumber = S.ProductNumber, T.Color = S.Color WHEN NOT MATCHED BY TARGET THEN INSERT (ProductID, Name, ProductNumber, Color) VALUES (S.ProductID, S.Name, S.ProductNumber, S.Color) WHEN NOT MATCHED BY SOURCE THEN DELETE;

注意,大部分繁重的工作是由合并条件及其结果完成的。不需要像在删除语句中那样反复设置匹配,它只做一次。

再次比较上面的插入语句和上面的合并语句。

INSERT INTO esqlProductTarget (ProductID, Name, ProductNumber, Color) SELECT S.ProductID, S.Name, S.ProductSource.ProductNumber, S.Color FROM esqlProductSource S WHERE NOT EXISTS (SELECT T.ProductID FROM esqlProductTarget T WHERE T.ProductID = S.ProductID)

鉴于MERGE语句建立了源表和目标表,以及它们如何匹配,所有用红色编码的内容都是多余的;因此,不在合并的插入部分。

可以使用OUTPUT子句来记录任何更改。在这种情况下,可以使用特殊变量$action来记录合并操作。这个变量将取三个值之一:“INSERT”、“UPDATE”或“DELETE”。

将继续使用例子,但这次将记录更改并总结更改。

MERGE esqlProductTarget T USING esqlProductSource S ON (S.ProductID = T.ProductID) WHEN MATCHED THEN UPDATE SET T.Name = S.Name, T.ProductNumber = S.ProductNumber, T.Color = S.Color WHEN NOT MATCHED BY TARGET THEN INSERT (ProductID, Name, ProductNumber, Color) VALUES (S.ProductID, S.Name, S.ProductNumber, S.Color) WHEN NOT MATCHED BY SOURCE THEN DELETE OUTPUT S.ProductID, $action into @MergeLog; SELECT MergeAction, count(*) FROM @MergeLog GROUP BY MergeAction
沪ICP备2024098111号-1
上海秋旦网络科技中心:上海市奉贤区金大公路8218号1幢 联系电话:17898875485