SQL使用Merge在一个语句中完成插入、更新和删除操作

sql server 2008版本开始支持Merge语句。

这使我们有机会使用一个SQL语句就能完成插入、更新和删除操作。

下面是示例：


create table #temp
(
	id int,
	[name] varchar(64),
	category varchar(64)
)
insert into #temp(id,name,category) values
(1,'前轮','轮组'),
(2,'车架','架子'),
(3,'删除','删除')

create table #temp2
(
	id int,
	[name] varchar(64),
	category varchar(64)
)
insert into #temp2(id,name,category) values
(1,'前轮','轮组'),
(2,'车架2','架子2'),
(4,'后轮','轮组')

merge #temp as target
using #temp2 as source on target.id=source.id
--1.对于条件匹配的数据（即两个表的交集），则更新目标表
when matched then update set target.name=source.name,target.category=source.category
--2.对于目标表不能匹配的数据（即目标表中不存在但源表存在的差集数据），则向目标表插入这部分差集数据
when not matched by target then insert (id,name,category) values(source.id, source.name, source.category)
--3.对于源表不能匹配的数据（即源表中不存在但目标表存在的差集数据），则删除这部分目标表中的差集数据
when not matched by source then delete
;--注意这行的这个分号，代表 merge 语句的结束，不可省略

select * from #temp
select * from #temp2
drop table #temp
drop table #temp2

一些复制粘贴来的参考资料

本资料采用复制原文的方式并略有修改，出处：sql server merge into 和update insert哪个效率更高

In this article, I am going to give a detailed explanation of how to use the SQL MERGE statement in SQL Server. The MERGE statement in SQL is a very popular clause that can handle inserts, updates, and deletes all in a single transaction without having to write separate logic for each of these. You can specify conditions on which you expect the MERGE statement to insert, update, or delete, etc.

在本文中，我将详细介绍如何在SQL Server中使用SQL MERGE语句。 SQL中的MERGE语句是一个非常受欢迎的子句，它可以在单个事务中处理所有插入，更新和删除操作，而不必为每个操作编写单独的逻辑。您可以指定希望MERGE语句插入，更新或删除等的条件。

Using the MERGE statement in SQL gives you better flexibility in customizing your complex SQL scripts and also enhances the readability of your scripts. The MERGE statement basically modifies an existing table based on the result of comparison between the key fields with another table in the context.

在SQL中使用MERGE语句可为您提供更大的灵活性，以自定义复杂SQL脚本，还可以增强脚本的可读性。 MERGE语句基本上根据键字段与上下文中另一个表之间的比较结果来修改现有表。

Figure 1 – MERGE Illustration

图1 –合并插图

The above illustration depicts how a SQL MERGE statement basically works. As you can see, there are two circles that represent two tables and can be considered as Source and a Target. The MERGE statement tries to compare the source table with the target table based on a key field and then do some of the processing. The MERGE statement actually combines the INSERT, UPDATE, and the DELETE operations altogether. Although the MERGE statement is a little complex than the simple INSERTs or UPDATEs, once you are able to master the underlying concept, you can easily use this SQL MERGE more often than using the individual INSERTs or UPDATEs.

上图描述了SQL MERGE语句的基本工作原理。如您所见，有两个圆圈代表两个表，可以视为源和目标。 MERGE语句尝试根据键字段将源表与目标表进行比较，然后进行一些处理。 MERGE语句实际上将INSERT，UPDATE和DELETE操作完全结合在一起。尽管MERGE语句比简单的INSERT或UPDATE稍微复杂一点，但是一旦您掌握了基本概念，就可以比使用单个INSERT或UPDATE更容易地更频繁地使用此SQL MERGE。

(Applications of the SQL MERGE statement)
In a typical SQL Data warehouse solution, it is often essential to maintain a history of data in the warehouse with a reference to the source data that is being fed to the ETL tool. A most common use case is while trying to maintain Slowly Changing Dimensions (SCD) in the data warehouse. In such cases, you need to insert new records into the data warehouse, remove or flag records from the warehouse which are not in the source anymore, and update the values of those in the warehouse which have been updated in the source.

在典型SQL数据仓库解决方案中，通常非常重要的一点是要维护仓库中的数据历史记录，并参考要馈送到ETL工具的源数据。最常见的用例是尝试维护数据仓库中的尺寸变化缓慢（SCD）。在这种情况下，您需要将新记录插入数据仓库，从仓库中删除或标记不再位于源中的记录，并更新仓库中已在源中更新的记录的值。

The SQL MERGE statement was introduced in the SQL Server 2008 edition which allowed great flexibility to the database programmers to simplify their messy code around the INSERT, UPDATE and DELETE statements while applying the logic to implement SCD in ETL.

SQL MERGE语句是在SQL Server 2008版中引入的，它为数据库程序员提供了极大的灵活性，可以简化他们在INSERT，UPDATE和DELETE语句周围的混乱代码，同时应用在ETL中实现SCD的逻辑。

(Optimizing the performance of the SQL MERGE statement)
There are a few aspects using which you can optimize the performance of your MERGE statements. Having said that, it means now you can write all your DML statements (INSERT, UPDATE, and DELETE) combined in a single statement. From a data processing perspective, this is quite helpful as it reduces the I/O operations from the disk for each of the three statements individually and now data is being read from the source only once.

使用几个方面可以优化MERGE语句的性能。话虽如此，这意味着现在您可以将所有DML语句（INSERT，UPDATE和DELETE）组合成一个语句。从数据处理的角度来看，这非常有帮助，因为它减少了针对三个语句中的每个语句从磁盘进行的I / O操作，现在仅从源读取一次数据。

Also, the performance of the MERGE statement greatly depends on the proper indexes being used to match both the source and the target tables. Apart from indexes, it is also essential that the join conditions are optimized as well. We should also try to filter the source table so that only necessary records are being fetched by the statement to do the necessary operations.

同样，MERGE语句的性能在很大程度上取决于用于匹配源表和目标表的适当索引。除索引外，还必须优化连接条件。我们还应该尝试过滤源表，以便该语句仅获取必要的记录以执行必要的操作。

(Hands-on with the MERGE statement)
Now that we have gathered enough information regarding how the MERGE statement works, lets us go ahead and try to implement the same practically. For the purpose of this tutorial, I am going to create a simple table and insert a few records in it. You can use the following SQL script to create the database and tables on your machine.

现在，我们已经收集了有关MERGE语句如何工作的足够信息，让我们继续尝试并实际实施相同的信息。出于本教程的目的，我将创建一个简单的表并在其中插入一些记录。您可以使用以下SQL脚本在计算机上创建数据库和表。


CREATE DATABASE SqlShackMergeDemo
GO

USE SqlShackMergeDemo
GO

CREATE TABLE SourceProducts(
    ProductID		INT,
    ProductName		VARCHAR(50),
    Price		DECIMAL(9,2)
)
GO
    
INSERT INTO SourceProducts(ProductID,ProductName, Price) VALUES(1,'Table',100)
INSERT INTO SourceProducts(ProductID,ProductName, Price) VALUES(2,'Desk',80)
INSERT INTO SourceProducts(ProductID,ProductName, Price) VALUES(3,'Chair',50)
INSERT INTO SourceProducts(ProductID,ProductName, Price) VALUES(4,'Computer',300)
GO

CREATE TABLE TargetProducts(
    ProductID		INT,
    ProductName		VARCHAR(50),
    Price		DECIMAL(9,2)
)
GO

INSERT INTO TargetProducts(ProductID,ProductName, Price) VALUES(1,'Table',100)
INSERT INTO TargetProducts(ProductID,ProductName, Price) VALUES(2,'Desk',180)
INSERT INTO TargetProducts(ProductID,ProductName, Price) VALUES(5,'Bed',50)
INSERT INTO TargetProducts(ProductID,ProductName, Price) VALUES(6,'Cupboard',300)
GO

SELECT * FROM SourceProducts
SELECT * FROM TargetProducts

Figure 2 – Sample Data inserted

图2 –插入的样本数据

Now that the database is ready, the next step I am going to perform is to apply the MERGE statement and try to get both the tables to synchronize with each other. The first operation that we are trying to see is how to manage the INSERTs. You can copy and paste the below SQL code to merge the new data from the source to the target table.

现在数据库已经准备好，下一步我要执行的是应用MERGE语句并尝试使两个表相互同步。我们试图看到的第一个操作是如何管理INSERT。您可以复制并粘贴下面SQL代码，以将新数据从源合并到目标表。

USE SqlShackMergeDemo
GO
    
MERGE TargetProducts AS Target
USING SourceProducts	AS Source
ON Source.ProductID = Target.ProductID
WHEN NOT MATCHED BY Target THEN
    INSERT (ProductID,ProductName, Price) 
    VALUES (Source.ProductID,Source.ProductName, Source.Price);

Figure 3 – MERGE operation performed on the source and target tables

图3 –对源表和目标表执行的MERGE操作

As you can see, the two records with ProductID 3 and 4, which were not present in the target table are now inserted. This operation is done by matching the source and the target tables based on the ProductID field.

如您所见，现在插入了目标表中不存在的两个具有ProductID 3和4的记录。通过根据ProductID字段匹配源表和目标表来完成此操作。

Now that we have learned how to insert records using the SQL MERGE statement, let us learn how to update the values in the same statement. In order to update the values, the ProductID field must have a common value in both the source and the target tables. Only then the database engine will be able to match the records and the update operation can be performed on the columns that have been specified.

既然我们已经了解了如何使用SQL MERGE语句插入记录，那么让我们学习如何更新同一条语句中的值。为了更新值，ProductID字段在源表和目标表中必须具有一个公共值。只有这样，数据库引擎才能匹配记录，并且可以对已指定的列执行更新操作。

    USE SqlShackMergeDemo
    GO

    MERGE TargetProducts AS Target
    USING SourceProducts AS Source
    ON Source.ProductID = Target.ProductID

    -- For Inserts
    WHEN NOT MATCHED BY Target THEN
        INSERT (ProductID,ProductName, Price) 
        VALUES (Source.ProductID,Source.ProductName, Source.Price)

    -- For Updates
    WHEN MATCHED THEN UPDATE SET
        Target.ProductName = Source.ProductName,
        Target.Price = Source.Price;

Figure 4 – Record updated using the MERGE statement

图4 –使用MERGE语句更新记录

As you can see in the figure above, the initial value for the product “Desk” in the target table was mentioned as “180.00”. When the SQL MERGE statement was executed, it updated the values for all the matched records that had an entry in the source. Also, if you notice the SQL script now, you can see that I have just added the update script after the insert statement, and that means all the inserts and the updates are being executed in the same script itself.

如上图所示，目标表中产品“ Desk”的初始值提到为“ 180.00”。执行SQL MERGE语句时，它将更新源中具有条目的所有匹配记录的值。另外，如果您现在注意到SQL脚本，则可以看到我刚刚在insert语句之后添加了更新脚本，这意味着所有插入和更新都在同一脚本本身中执行。

Let us now see how to delete or remove records from the target table in the same script itself.

现在让我们看看如何在同一脚本本身中从目标表中删除或删除记录。


USE SqlShackMergeDemo
GO

MERGE TargetProducts AS Target
USING SourceProducts AS Source
ON Source.ProductID = Target.ProductID

-- For Inserts
WHEN NOT MATCHED BY Target THEN
    INSERT (ProductID,ProductName, Price) 
    VALUES (Source.ProductID,Source.ProductName, Source.Price)

-- For Updates
WHEN MATCHED THEN UPDATE SET
    Target.ProductName = Source.ProductName,
    Target.Price = Source.Price

-- For Deletes
WHEN NOT MATCHED BY Source THEN
    DELETE;

Figure 5 – Records deleted using the MERGE statement

图5 –使用MERGE语句删除的记录

Now, if you see, all records with ProductID 5 and 6 are being deleted from the target table since these records are not available in the source. In this way, you can implement a SQL MERGE statement in a very simple yet powerful way and can handle complex business requirements.

现在，如果您看到的话，所有具有ProductID 5和6的记录都将从目标表中删除，因为这些记录在源中不可用。这样，您可以以非常简单但功能强大的方式实现SQL MERGE语句，并且可以处理复杂的业务需求。

If you would like to see a summary of all the actions that have been performed by the MERGE statement, then you may modify your existing script and include the following output actions. It will return us a list of records on which we have performed the merge and what operation has been executed on that particular record.

如果要查看MERGE语句已执行的所有操作的摘要，则可以修改现有脚本并包括以下输出操作。它将向我们返回一个记录列表，在该记录上我们执行了合并以及对该特定记录执行了哪些操作。

USE SqlShackMergeDemo
GO

MERGE TargetProducts AS Target
USING SourceProducts AS Source
ON Source.ProductID = Target.ProductID

-- For Inserts
WHEN NOT MATCHED BY Target THEN
    INSERT (ProductID,ProductName, Price)
    VALUES (Source.ProductID,Source.ProductName, Source.Price)

-- For Updates
WHEN MATCHED THEN UPDATE SET
    Target.ProductName = Source.ProductName,
    Target.Price = Source.Price

-- For Deletes
WHEN NOT MATCHED BY Source THEN
    DELETE

-- Checking the actions by MERGE statement
OUTPUT $action,
DELETED.ProductID AS TargetProductID,
DELETED.ProductName AS TargetProductName,
DELETED.Price AS TargetPrice,
INSERTED.ProductID AS SourceProductID,
INSERTED.ProductName AS SourceProductName,
INSERTED.Price AS SourcePrice;

Figure 6 – Checking output actions by the merge statement

图6 –通过merge语句检查输出操作

(Important things to remember while implementing SQL MERGE)
Although we have now understood how to write the MERGE statement from scratch and how to modify the script to include logic for handling inserts, updates and deletes, there are also some other key important points that we should keep in mind while preparing the scripts.

尽管我们现在已经了解了如何从头开始编写MERGE语句，以及如何修改脚本以包括用于处理插入，更新和删除的逻辑，但是在编写脚本时，我们还应牢记其他一些关键的重要点。

1.Every MERGE statement must end with a semi-colon. If a semi-colon is not present at the end of the MERGE statement, then an error will be thrown
2.You can use SELECT @@RowCount after writing the MERGE statement which will return the number of records that have been modified by the transaction
3.It is mandatory that one of the MATCHED clauses is provided in order for the MERGE statement to operate

每个MERGE语句都必须以分号结尾。如果MERGE语句的末尾不存在分号，则将引发错误。
您可以在编写MERGE语句后使用SELECT@@RowCount，该语句将返回事务已修改的记录数。
必须提供用于匹配条件的子句，以便MERGE语句进行操作。

(Conclusion)
In this article, I have explained in detail about the SQL MERGE statement. This MERGE statement has been introduced in the SQL Server 2008 which brought a great revolution in writing simpler and maintainable code in SQL. The MERGE statement takes in two tables – a source and a target and compares the records based on a key column, often the index column, and then performs an operation on it. Being a database developer, I would definitely advise all young programmers to start using the SQL MERGE statement more frequently while using complex stored procedures in SQL.

在本文中，我已经详细解释了有关SQL MERGE语句的信息。该MERGE语句已在SQL Server 2008中引入，这为用SQL编写更简单和可维护的代码带来了巨大的革命。 MERGE语句接受两个表-源表和目标表，并根据键列（通常是索引列）比较记录，然后对其执行操作。作为数据库开发人员，我绝对建议所有年轻程序员在SQL中使用复杂的存储过程时，开始更频繁地使用SQL MERGE语句。

posted on 2024-07-13 18:50 hrx521 阅读(1) 评论(0) 编辑收藏举报