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

今天在一个存储过程中看见了merge这个关键字，第一个想法是，这个是配置管理中的概念吗，把相邻两次的更改合并到一起。后来在technet上搜索发现别有洞天，原来是另外一个sql关键字，t-sql的语法还是相当地丰富的。本篇是一篇学习笔记，没有什么新意，这里给

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

　　

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

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

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

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

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

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

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

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

<p><span>1</span> <span>exec</span><span> InsertUnitMeasure </span><span>@UnitMeasureCode</span><span>=</span><span>'</span><span>ABC</span><span>'</span><span>,</span><span>@Name</span><span>=</span><span>'</span><span>New Test Value1</span><span>'</span><span><br></span><span>2</span> <span>EXEC</span><span> InsertUnitMeasure </span><span>@UnitMeasureCode</span><span>=</span><span>'</span><span>XYZ</span><span>'</span><span>, </span><span>@Name</span><span>=</span><span>'</span><span>Test Value</span><span>'</span><span>;<br></span><span>3</span> <span>EXEC</span><span> InsertUnitMeasure </span><span>@UnitMeasureCode</span><span>=</span><span>'</span><span>ABC</span><span>'</span><span>, </span><span>@Name</span><span>=</span><span>'</span><span>Another Test Valuea</span><span>'</span><span>;<br></span><span>4</span> <span>Go</span></p>

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

图1

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

图2

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

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

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

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

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

EXECUTE Production.usp_UpdateInventory '20030501'

图3

　　

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

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

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

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

图4

　　

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

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

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

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

图5

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