Using MERGE in SQL Server to insert, update and delete at the same time
来源:互联网 发布:提分宝典的真实性 知乎 编辑:程序博客网 时间:2024/03/29 14:00
Using MERGE in SQL Server to insert, update and delete at the same time
Written By: Arshad Ali -- 3/10/2009 -- 2 comments
Stay informed - get the MSSQLTips.com newsletter and win - click here
ProblemIn a typical data warehousing application, quite often during the ETL cycle you need to perform INSERT, UPDATE and DELETE operations on a TARGET table by matching the records from the SOURCE table. For example, a products dimension table has information about the products; you need to sync-up this table with the latest information about the products from the source table. You would need to write separate INSERT, UPDATE and DELETE statements to refresh the target table with an updated product list or do lookups. Though it seems to be straight forward at first glance, but it becomes cumbersome when you have do it very often or on multiple tables, even the performance degrades significantly with this approach. In this tip we will walk through how to use the MERGE statement and do this in one pass.
Solution
Beginning with SQL Server 2008, now you can use MERGE SQL command to perform these operations in a single statement. This new command is similar to the UPSERT (fusion of the words UPDATE and INSERT.) command of Oracle; it inserts rows that don’t exist and updates the rows that do exist. With the introduction of the MERGE SQL command, developers can more effectively handle common data warehousing scenarios, like checking whether a row exists, and then executing an insert or update or delete.
The MERGE statement basically merges data from a source result set to a target table based on a condition that you specify and if the data from the source already exists in the target or not. The new SQL command combines the sequence of conditional INSERT, UPDATE and DELETE commands in a single atomic statement, depending on the existence of a record. The new MERGE SQL command looks like as below:
MERGE <target_table> [AS TARGET]USING <table_source> [AS SOURCE]ON <search_condition>[WHEN MATCHED THEN <merge_matched> ][WHEN NOT MATCHED [BY TARGET]THEN <merge_not_matched> ][WHEN NOT MATCHED BY SOURCETHEN <merge_ matched> ];
The MERGE statement basically works as separate insert, update, and delete statements all within the same statement. You specify a "Source" record set and a "Target" table, and the join between the two. You then specify the type of data modification that is to occur when the records between the two data are matched or are not matched. MERGE is very useful, especially when it comes to loading data warehouse tables, which can be very large and require specific actions to be taken when rows are or are not present.
Putting it all together
In this example I will take a Products table as target table and UpdatedProducts as a source table containing updated list of products. I will then use the MERGE SQL command to synchronize the target table with the source table.
First Let's create a target table and a source table and populate some data to these tables.
MERGE SQL statement - Part 1
--Create a target tableCREATE TABLE Products(ProductID INT PRIMARY KEY,ProductName VARCHAR(100),Rate MONEY) GO--Insert records into target tableINSERT INTO ProductsVALUES(1, 'Tea', 10.00),(2, 'Coffee', 20.00),(3, 'Muffin', 30.00),(4, 'Biscuit', 40.00)GO--Create source tableCREATE TABLE UpdatedProducts(ProductID INT PRIMARY KEY,ProductName VARCHAR(100),Rate MONEY) GO--Insert records into source tableINSERT INTO UpdatedProductsVALUES(1, 'Tea', 10.00),(2, 'Coffee', 25.00),(3, 'Muffin', 35.00),(5, 'Pizza', 60.00)GOSELECT * FROM ProductsSELECT * FROM UpdatedProductsGO
Next I will use the MERGE SQL command to synchronize the target table with the refreshed data coming from the source table.
MERGE SQL statement - Part 2
--Synchronize the target table with--refreshed data from source tableMERGE Products AS TARGETUSING UpdatedProducts AS SOURCE ON (TARGET.ProductID = SOURCE.ProductID) --When records are matched, update --the records if there is any changeWHEN MATCHED AND TARGET.ProductName <> SOURCE.ProductName OR TARGET.Rate <> SOURCE.Rate THEN UPDATE SET TARGET.ProductName = SOURCE.ProductName, TARGET.Rate = SOURCE.Rate --When no records are matched, insert--the incoming records from source--table to target tableWHEN NOT MATCHED BY TARGET THEN INSERT (ProductID, ProductName, Rate) VALUES (SOURCE.ProductID, SOURCE.ProductName, SOURCE.Rate)--When there is a row that exists in target table and--same record does not exist in source table--then delete this record from target tableWHEN NOT MATCHED BY SOURCE THEN DELETE--$action specifies a column of type nvarchar(10) --in the OUTPUT clause that returns one of three --values for each row: 'INSERT', 'UPDATE', or 'DELETE', --according to the action that was performed on that rowOUTPUT $action, DELETED.ProductID AS TargetProductID, DELETED.ProductName AS TargetProductName, DELETED.Rate AS TargetRate, INSERTED.ProductID AS SourceProductID, INSERTED.ProductName AS SourceProductName, INSERTED.Rate AS SourceRate; SELECT @@ROWCOUNT;GO
When the above is run this is the output. There were 2 updates, 1 delete and 1 insert.
If we select all records from the Products table we can see the final results. We can see the Coffee rate was updated from 20.00 to 25.00, the Muffin rate was updated from 30.00 to 35.00, Biscuit was deleted and Pizza was inserted.
Notes
- The MERGE SQL statement requires a semicolon (;) as a statement terminator. Otherwise Error 10713 is raised when a MERGE statement is executed without the statement terminator.
- When used after MERGE, @@ROWCOUNT returns the total number of rows inserted, updated, and deleted to the client.
- At least one of the three MATCHED clauses must be specified when using MERGE statement; the MATCHED clauses can be specified in any order. However a variable cannot be updated more than once in the same MATCHED clause.
- Of course it’s obvious, but just to mention, the person executing the MERGE statement should have SELECT Permission on the SOURCE Table and INSERT, UPDATE and DELETE Permission on the TARGET Table.
- MERGE SQL statement improves the performance as all the data is read and processed only once whereas in previous versions three different statements have to be written to process three different activities (INSERT, UPDATE or DELETE) in which case the data in both the source and target tables are evaluated and processed multiple times; at least once for each statement.
- MERGE SQL statement takes same kind of locks minus one Intent Shared (IS) Lock that was due to the select statement in the ‘IF EXISTS’ as we did in previous version of SQL Server.
- For every insert, update, or delete action specified in the MERGE statement, SQL Server fires any corresponding AFTER triggers defined on the target table, but does not guarantee on which action to fire triggers first or last. Triggers defined for the same action honor the order you specify.
Next Steps
- Review “MERGE (Transact-SQL)” on MSDN.
- Review Chad Boyd' blog.
- Using MERGE in SQL Server to insert, update and delete at the same time
- Git问题:Cannot update paths and switch to branch 'dev' at the same time.
- fatal: Cannot update paths and switch to branch 'gitlab' at the same time.
- Cannot update paths and switch to branch 'snapshot_mixuse' at the same time.
- Cannot update paths and switch to branch at the same time
- fatal: Cannot update paths and switch to branch 'master' at the same time.
- How to add css class and id in @Html.TextBox mvc4 at the same time
- clone 远程分支 Cannot update paths and switch to branch 'dev' at the same time. Did you intend to checko
- git checkout -b时fatal: Cannot update paths and switch to branch '' at the same time.解决方案
- SQL INSERT TO &UPDATE&DELETE
- Insert, Update, Delete in ASP.NET Gridview, DataSource as SQL Server, MS Access (mdb/accdb), XML and
- how to play music and record from the mic at the same time?
- Insert and Update image field in SQL Server 2008/2005
- 在SQL Server中使用merge同时进行Insert,Update和Delete操作
- SQL Server的Merge —— 一步实现 insert,update,delete
- sql server merge 做insert和update
- How can a database be in-memory and durable at the same time?
- Could I use NEON and VFP at the same time in my application?
- CSDN 2009中国软件技术英雄会(北京站) 之 我评的奖
- 申明
- MISRA C指导指南解读系列2(MISRA 规则 1- 10)
- Window平台---IPSEC客户端的安装
- 巧用SQL的全局临时表防止用户重复登录
- Using MERGE in SQL Server to insert, update and delete at the same time
- target的应用
- Windows Automated Installation Kit Windows 7 Beta
- Ehcache使用用例(一)
- 修理老板的好办法
- 模式识别科学发展与现状(4.展望)
- 认识自己
- Onpaint和OnDraw的区别
- 话说页面兼容不同浏览器