开发者社区> 杰克.陈> 正文

SQL点滴18—SqlServer中的merge操作,相当地风骚

简介: 原文:SQL点滴18—SqlServer中的merge操作,相当地风骚 今天在一个存储过程中看见了merge这个关键字,第一个想法是,这个是配置管理中的概念吗,把相邻两次的更改合并到一起。后来在technet上搜索发现别有洞天,原来是另外一个sql关键字,t-sql的语法还是相当地丰富的。
+关注继续查看
原文:SQL点滴18—SqlServer中的merge操作,相当地风骚

今天在一个存储过程中看见了merge这个关键字,第一个想法是,这个是配置管理中的概念吗,把相邻两次的更改合并到一起。后来在technet上搜索发现别有洞天,原来是另外一个sql关键字,t-sql的语法还是相当地丰富的。本篇是一篇学习笔记,没有什么新意,这里给出technet上的地址连接供大家参考权威:http://technet.microsoft.com/zh-cn/library/bb510625.aspx,这里具体的语法不去深究了,只是把几个例子实际运行,剖析一番。

  

使用merge同时执行insert和update操作

我们经常会有这样的需求,根据某个字段或多个字段查找表中的一行或多行数据,如果查找成功得到匹配项,更新其中的其他一个或多个字段;如果查找失败则将“某个字段或多个字段”作为新的一行中的数据插入到表中。第一种方法是先更新,然后根据@@rowcount判断是否有匹配项,如果没有则插入。先使用下面的 代码创建一个存储过程。


1 use AdventureWorks
2 go
3 create procedure dbo.InsertUnitMeasure @UnitMeasureCode nchar(3),@Name nvarchar(25)
4 as
5 begin
6 set nocount on;
7 update Production.UnitMeasure set Name=@Name where UnitMeasureCode=@UnitMeasureCode
8 if(@@ROWCOUNT=0)
9 begin
10 insert into Production.UnitMeasure(Name,UnitMeasureCode)values(@Name,@UnitMeasureCode)
11 end
12 end
13 go

记得见过这样的笔试题目,要求是插入不存在的行,只要把上面语句中的update改成select就可以了,当时没有写出来,现在恍然大悟,也许是在考察@@ROWCOUNT的用法吧。这个语句也可以使用merge语句实现。下面我们使用merge关键字来修改这个存储过程。


1 alter procedure dbo.InsertUnitMeasure @UnitMeasureCode nchar(3),@Name nvarchar(25)
2 as
3 begin
4 set nocount on
5 merge Production.UnitMeasure as target
6 using (select @UnitMeasureCode,@Name) as source (UnitMeasureCode,Name)
7 on (target.UnitMeasureCode=source.UnitMeasureCode)
8 when matched then update set Name=source.Name
9 when not matched then insert(UnitMeasureCode,Name)values(source.UnitMeasureCode,Name)
10 output deleted.*,$action,inserted.* into MyTempTable;
11 end
12 go

这个语句使用merge修改存储过程,这个语句中又出现我不太了解的关键字using和$action。Using是用来指定和表InsertUnitMeasure中相匹配的数据源,这里的数据源来自外部输入,是通过两个输入参数得到。$action可能是一个占位符,表示上面的when字句进行的操作。至于inserted.*和deleted.* 就是插入和删除的数据行了,这个我在其中一篇文章中也提到,他们有点类似类中的this关键字,过可以看看:SQL点滴14—编辑数据。注意为了记录修改的过程我们需要创建一个临时表#MyTempTable来跟踪修改过程,所以在调用这个存储过程之前我们需要新建这个表,语句如下:


1 create table MyTempTable(
2 ExistingCode nchar(3),
3 ExistingName nvarchar(50),
4 ExistingDate datetime,
5 ActionTaken nvarchar(50),
6 NewCode nchar(3),
7 [NewName] nvarchar(50),
8 NewDate datetime
9 )
10 Go

现在我们来执行下面的语句看看有什么样的结果:


1 exec InsertUnitMeasure @UnitMeasureCode = 'ABC',@Name='New Test Value1'
2 EXEC InsertUnitMeasure @UnitMeasureCode = 'XYZ', @Name = 'Test Value';
3 EXEC InsertUnitMeasure @UnitMeasureCode = 'ABC', @Name = 'Another Test Valuea';
4 Go

首先使用语句:select * from Production.UnitMeasure order by ModifiedDate desc 来查看目标表中的数据变化如图1:img_a4f8b52e8c789f02b080b7f1b5714309.png

图1

这里虽然三次执行了存储过程,但是由于第一次和第三次的@UnitMeasureCode的值是相同的’ABC’所以第二次肯定是进行更新操作。所以最后表中新增了两条记录。然后使用下面的语句查看记录表MyTempTable中的跟踪信息如图2

img_aa166430f1f4c070cb3cc60bf56987ce.png

图2

我们可以看到前面两条语句执行的是插入操作,所以原有的值都是空,因为在插入之前他们还不存在。第三条新型的是更新操作,更新UnitMeasureCode为’ABC’的记录。
 
  

使用merge在单个语句中执行insert和update操作

在AdventureWorks数据库中有ProductInventory表,存储的是存货信息,SalesOrderDetail表中存储的是订单信息,现在如果每天减去对SalesOrderDetail表中每种产品所下的订单数,更新ProductInventory表中的 Quantity列。如果随着时间推移订单数导致产品库存量下降到0或者更少,则从ProductInventory表中删除该产品对应的行。下面的语句创建一个存储过程实现上面的逻辑。


1 CREATE PROCEDURE Production.usp_UpdateInventory
2 @OrderDate datetime
3 AS
4 MERGE Production.ProductInventory AS target
5 USING (SELECT ProductID, SUM(OrderQty) FROM Sales.SalesOrderDetail AS sod
6 JOIN Sales.SalesOrderHeader AS soh
7 ON sod.SalesOrderID = soh.SalesOrderID
8 AND soh.OrderDate = @OrderDate
9 GROUP BY ProductID) AS source (ProductID, OrderQty)
10 ON (target.ProductID = source.ProductID)
11 WHEN MATCHED AND target.Quantity - source.OrderQty <= 0
12 THEN DELETE
13 WHEN MATCHED
14 THEN UPDATE SET target.Quantity = target.Quantity - source.OrderQty,
15 target.ModifiedDate = GETDATE()
16 OUTPUT $action, Inserted.ProductID, Inserted.Quantity, Inserted.ModifiedDate, Deleted.ProductID,
17 Deleted.Quantity, Deleted.ModifiedDate;
18 GO

这个语句比第一个要复杂一点,注意当匹配成功并且总量小于0的时候直接使用一个delete就可以将此条记录删除,output语句直接把操作结果输出,相当地神奇。最后运行下面的 语句得到如图3的结果。注意这个语句相当于将2003年5月1号的订单量减去。如果多次运行的话就相当于多减了一次,整个表中数据条数会减少的。

EXECUTE Production.usp_UpdateInventory '20030501'

img_234dd42255973433fc71b4fc810cc993.png

图3

  

借助派生源表,使用merge对目标表执行update和insert操作

这次我们已知有一些表数据,我们要和Sales.SalesReason这个表中的数据做对比,如果和SalesReason表中的Name字段匹配时就更新表中的ReasonType列,如果没有匹配项的时候就插入这一行新的数据。在这里是使用表值构造函数指定源表的多个行,使用表变量存储更新记录,注意表变量的使用范围。代码如下:


1 declare @SummaryOfChanges table(Change varchar(20))
2 merge into Sales.SalesReason as target
3 using(values('Recommendation','Other'),('Review','Marketing'),('Internet','Promotion')) as source([NewName],NewReasonType)
4 on target.Name=source.[NewName]
5 when matched then update set ReasonType=source.NewReasonType
6 when not matched by target then insert(Name,ReasonType) values ([NewName],NewReasonType)
7 output $action into @SummaryOfChanges;
8 select Change,COUNT(*) as CountPerChange from @SummaryOfChanges group by Change

执行完上面的语句之后我们得到下面的结果说明执行了2次插入,1次更新,如图4。那么是不是这样的 呢,我们查看Sales.SalesReason这个表发现原来已经有’Review’这一条数据了,对它执行了更新,剩下的’Recommendation’,’Internet’执行的是插入操作。如果再次执行上面的语句就会得到UPDATE 3这样的结果,因为已经存在这三条数据了所以都执行UPDATE。

img_11c1583f03dd5047c6560b7c5e348f7c.png

图4

  

将merge执行的结果插入到另外一个表中

我们还可以将merge操作得到的结果写入到另外一个表中,如下的语句将更新的每条数据信息写入到一个新建的表Production.UpdatedInventory中,代码如下:


1 INSERT INTO Production.UpdatedInventory
2 SELECT ProductID, LocationID, NewQty, PreviousQty
3 FROM
4 ( MERGE Production.ProductInventory AS target
5 USING (SELECT ProductID, SUM(OrderQty)
6 FROM Sales.SalesOrderDetail AS sod
7 JOIN Sales.SalesOrderHeader AS soh
8 ON sod.SalesOrderID = soh.SalesOrderID
9 AND soh.OrderDate BETWEEN '20030701' AND '20030731'
10 GROUP BY ProductID) AS source (ProductID, OrderQty)
11 ON target.ProductID = source.ProductID
12 WHEN MATCHED AND target.Quantity - source.OrderQty >= 0
13 THEN UPDATE SET target.Quantity = target.Quantity - source.OrderQty
14 WHEN MATCHED AND target.Quantity - source.OrderQty <= 0
15 THEN DELETE
16 OUTPUT $action, Inserted.ProductID, Inserted.LocationID, Inserted.Quantity AS NewQty, Deleted.Quantity AS PreviousQty)
17 AS Changes (Action, ProductID, LocationID, NewQty, PreviousQty) WHERE Action = 'UPDATE';
18 GO

执行这个语句再查询表得到如下图5的结果,我们可以看到新的销售量总是比以前的销售量要少,因为执行一次就要减去订单量。

img_fa8b697ba328df73dee41892ba2beea8.png

图5

这里我们只记录了更新的变化,如果想记录所有的操作可以去掉最后的一个限制条件WHERE Action = 'UPDATE',那就要修改记录表的结构了,这个和第二个例子有些相似,只不过将记录在实际的表中,而第二个例子仅仅输出这些操作记录。

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
阿里云服务器端口号设置
阿里云服务器初级使用者可能面临的问题之一. 使用tomcat或者其他服务器软件设置端口号后,比如 一些不是默认的, mysql的 3306, mssql的1433,有时候打不开网页, 原因是没有在ecs安全组去设置这个端口号. 解决: 点击ecs下网络和安全下的安全组 在弹出的安全组中,如果没有就新建安全组,然后点击配置规则 最后如上图点击添加...或快速创建.   have fun!  将编程看作是一门艺术,而不单单是个技术。
18616 0
操作SQL Server的五种错误做法
http://www.searchdatabase.com.cn/showcontent_41087.htm
393 0
Sql Server之旅——第十站 看看DML操作对索引的影响
原文:Sql Server之旅——第十站 看看DML操作对索引的影响   我们都知道建索引是需要谨慎的,当只有利大于弊的时候才适合建,我们也知道建索引是需要维护成本的,这个维护也就在于DML操作了, 下面我们具体看看到底DML对索引都有哪些内幕。
811 0
JDBC操作 SQL SERVER之数据的分页显示
  分页显示数据记录 =countRow)    break ;     }     if(pages==1)          out.print("共" +countPage+"页,当前第"+pages+"页,下一页") ;   else   if(pages==countPage)    {     out.
877 0
SQLServer 2008以上误操作数据库恢复方法——日志尾部备份
原文: SQLServer 2008以上误操作数据库恢复方法——日志尾部备份 原文出处:http://blog.csdn.net/dba_huangzj/article/details/8491327 问题:          经常看到有人误删数据,或者误操作,特别是update和delete的时候没有加where,然后就喊爹喊娘了。
1285 0
+关注
杰克.陈
一个安静的程序猿~
10427
文章
2
问答
文章排行榜
最热
最新
相关电子书
更多
JS零基础入门教程(上册)
立即下载
性能优化方法论
立即下载
手把手学习日志服务SLS,云启实验室实战指南
立即下载