SQL Server 学习之路（九）

前面一直都在讲数据查询语言(DQL)方面的知识点，今天我们来说说数据操作语言(DML)方面的内容。但是不是讲INSERT，UPDATE和DELETE，而是这三者的集合MERGE。

在 SQL Server 2008 中及以后的版本当中，我们可以使用 MERGE 语句在一条语句中执行插入、更新或删除操作。MERGE 语句允许我们将数据源与目标表或视图联接，然后根据该联接的结果对目标执行多项操作。例如，可以使用 MERGE 语句执行以下操作：

有条件地在目标表中插入或更新行。

如果目标表中存在相应行，则更新一个或多个列；否则，会将数据插入新行。

同步两个表。

根据与源数据的差别在目标表中插入、更新或删除行。

MERGE 语法包括五个主要子句：(具体的语法结构大家自行百度一下)

MERGE 子句用于指定作为插入、更新或删除操作目标的表或视图。

USING 子句用于指定要与目标联接的数据源。

ON 子句用于指定决定目标与源的匹配位置的联接条件。

WHEN 子句（WHEN MATCHED、WHEN NOT MATCHED BY TARGET 和 WHEN NOT MATCHED BY SOURCE）基于 ON 子句的结果和在 WHEN 子句中指定的任何其他搜索条件指定所要采取的操作。

OUTPUT 子句针对插入、更新或删除的目标中的每一行返回一行。

相关示例：

A.使用简单的 MERGE 语句执行 INSERT 和 UPDATE 操作

假定您在数据仓库数据库中有一个FactBuyingHabits表，该表用于跟踪每个客户购买特定产品的最后日期。OLTP 数据库中的第二个表Purchases用于记录给定周的购买情况。您每周都要从Purchases表向FactBuyingHabits表中添加特定客户以前从未购买过的产品的行。对于购买以前曾经购买过的产品的客户的行，您只需更新FactBuyingHabits表中的购买日期即可。可以使用 MERGE 在一条语句中执行这些插入和更新操作。

USE AdventureWorks2008R2;

GO

IF OBJECT_ID (N'dbo.Purchases', N'U') IS NOT NULL

DROP TABLE dbo.Purchases;

GO

CREATE TABLE dbo.Purchases (

ProductID int, CustomerID int, PurchaseDate datetime,

CONSTRAINT PK_PurchProdID PRIMARY KEY(ProductID,CustomerID));

GO

INSERT INTO dbo.Purchases VALUES(707, 11794, '20060821'),

(707, 15160, '20060825'),(708, 18529, '20060821'),

(711, 11794, '20060821'),(711, 19585, '20060822'),

(712, 14680, '20060825'),(712, 21524, '20060825'),

(712, 19072, '20060821'),(870, 15160, '20060823'),

(870, 11927, '20060824'),(870, 18749, '20060825');

GO

IF OBJECT_ID (N'dbo.FactBuyingHabits', N'U') IS NOT NULL

DROP TABLE dbo.FactBuyingHabits;

GO

CREATE TABLE dbo.FactBuyingHabits (

ProductID int, CustomerID int, LastPurchaseDate datetime,

CONSTRAINT PK_FactProdID PRIMARY KEY(ProductID,CustomerID));

GO

INSERT INTO dbo.FactBuyingHabits VALUES(707, 11794, '20060814'),

(707, 18178, '20060818'),(864, 14114, '20060818'),

(866, 13350, '20060818'),(866, 20201, '20060815'),

(867, 20201, '20060814'),(869, 19893, '20060815'),

(870, 17151, '20060818'),(870, 15160, '20060817'),

(871, 21717, '20060817'),(871, 21163, '20060815'),

(871, 13350, '20060815'),(873, 23381, '20060815');

GO

--MERGE操作

MERGE dbo.FactBuyingHabits AS Target

USING (SELECT CustomerID, ProductID, PurchaseDate FROM dbo.Purchases) AS Source

ON (Target.ProductID = Source.ProductID AND Target.CustomerID = Source.CustomerID)

WHEN MATCHED THEN

UPDATE SET Target.LastPurchaseDate = Source.PurchaseDate

WHEN NOT MATCHED BY TARGET THEN

INSERT (CustomerID, ProductID, LastPurchaseDate)

VALUES (Source.CustomerID, Source.ProductID, Source.PurchaseDate)

OUTPUT $action, Inserted.*, Deleted.*;

B. 使用 MERGE 在单个语句中对表执行 UPDATE 和 DELETE 操作

使用 MERGE 根据 SalesOrderDetail 表中已处理的订单，每天更新 AdventureWorks2008R2 示例数据库中的 ProductInventory 表。使用以下 MERGE 语句后，ProductInventory 表的 Quantity 列将通过减去每天为每种产品所下订单数的方式进行更新。如果某种产品的订单数导致该产品的库存下降为 0 或 0 以下，则会从 ProductInventory 表中删除该产品的行。

USE AdventureWorks2008R2;

GO

IF OBJECT_ID (N'Production.usp_UpdateInventory', N'P') IS NOT NULL

DROP PROCEDURE Production.usp_UpdateInventory;

GO

CREATE PROCEDURE Production.usp_UpdateInventory

@OrderDate datetime

AS

MERGE Production.ProductInventory AS target

USING (SELECT ProductID, SUM(OrderQty) FROM Sales.SalesOrderDetail AS sod

JOIN Sales.SalesOrderHeader AS soh

ON sod.SalesOrderID = soh.SalesOrderID

AND soh.OrderDate = @OrderDate

GROUP BY ProductID) AS source (ProductID, OrderQty)

ON (target.ProductID = source.ProductID)

WHEN MATCHED AND target.Quantity - source.OrderQty

THEN DELETE

WHEN MATCHED

THEN UPDATE SET target.Quantity = target.Quantity - source.OrderQty,

target.ModifiedDate = GETDATE()

OUTPUT $action, Inserted.ProductID, Inserted.Quantity, Inserted.ModifiedDate, Deleted.ProductID,

Deleted.Quantity, Deleted.ModifiedDate;

GO

EXECUTE Production.usp_UpdateInventory '20030501'

C. 借助派生的源表，使用 MERGE 对目标表执行 UPDATE 和 INSERT 操作

下面的示例使用 MERGE 以更新或插入行的方式来修改 SalesReason 表。当源表中的 NewName 值与目标表 (SalesReason) 的 Name 列中的值匹配时，就会更新此目标表中的 ReasonType 列。当 NewName 的值不匹配时，就会将源行插入到目标表中。此源表是一个派生表，它使用 Transact-SQL 表值构造函数指定源表的多个行。该示例还说明了如何在表变量中存储 OUTPUT 子句的结果，并且说明存储结果之后如何通过执行返回已插入和更新的行的计数的简单选择操作来汇总 MERGE 语句的结果。

USE AdventureWorks2008R2;

GO

DECLARE @SummaryOfChanges TABLE(Change VARCHAR(20));

MERGE INTO Sales.SalesReason AS Target

USING (VALUES ('Recommendation','Other'), ('Review', 'Marketing'), ('Internet', 'Promotion'))

AS Source (NewName, NewReasonType)

ON Target.Name = Source.NewName

WHEN MATCHED THEN

UPDATE SET ReasonType = Source.NewReasonType

WHEN NOT MATCHED BY TARGET THEN

INSERT (Name, ReasonType) VALUES (NewName, NewReasonType)

OUTPUT $action INTO @SummaryOfChanges;

--查询表变量的结果

SELECT Change, COUNT(*) AS CountPerChange

FROM @SummaryOfChanges

GROUP BY Change;

D.使用MERGE执行 INSERT、UPDATE 和 DELETE 操作

MERGE 基于与源数据的差别在目标表中插入、更新或删除行。假设有一个小公司，该公司有五个部门，每个部门有一位部门经理。该公司决定对这些部门进行重组。若要在目标表 dbo.Departments 中实现重组结果，MERGE 语句必须实现以下更改：

现有的一些部门将不会变化。

现有的一些部门将任命新的经理。

将会新建一些部门。

一些部门在重组后将不再存在。

--创建目标表 dbo.Departments，并在表中填充相应的经理。

USE AdventureWorks2008R2;

GO

IF OBJECT_ID (N'dbo.Departments', N'U') IS NOT NULL

DROP TABLE dbo.Departments;

GO

CREATE TABLE dbo.Departments (DeptID tinyint NOT NULL PRIMARY KEY, DeptName nvarchar(30),

Manager nvarchar(50));

GO

INSERT INTO dbo.Departments

VALUES (1, 'Human Resources', 'Margheim'),(2, 'Sales', 'Byham'),

(3, 'Finance', 'Gill'),(4, 'Purchasing', 'Barber'),

(5, 'Manufacturing', 'Brewer');

--对部门所做的组织更改存储在源表 dbo.Departments_delta 中。

USE AdventureWorks2008R2;

GO

IF OBJECT_ID (N'dbo.Departments_delta', N'U') IS NOT NULL

DROP TABLE dbo.Departments_delta;

GO

CREATE TABLE dbo.Departments_delta (DeptID tinyint NOT NULL PRIMARY KEY, DeptName nvarchar(30),

Manager nvarchar(50));

GO

INSERT INTO dbo.Departments_delta VALUES

(1, 'Human Resources', 'Margheim'), (2, 'Sales', 'Erickson'),

(3 , 'Accounting', 'Varkey'),(4, 'Purchasing', 'Barber'),

(6, 'Production', 'Jones'), (7, 'Customer Relations', 'Smith');

GO

最后，为在目标表中反映公司重组，以下代码使用 MERGE 语句将源表 dbo.Departments_delta 与目标表 dbo.Departments 进行比较。此比较的搜索条件在该语句的 ON 子句中定义。根据比较的结果，将执行以下操作。

在表 Departments 中，在源表和目标表中都存在的部门都将使用新名称、新经理或这两者进行更新。如果没有变化，则不进行任何更新。这是通过 WHEN MATCHED THEN 子句完成的。

在 Departments 中不存在但存在于 Departments_delta 中的所有部门，将插入到 Departments 中。这是通过 WHEN NOT MATCHED THEN 子句完成的。

在 Departments_delta 中不存在但存在于 Departments 中的所有部门将从 Departments 中删除。这是通过 WHEN NOT MATCHED BY SOURCE THEN 子句完成的。

MERGE dbo.Departments AS d

USING dbo.Departments_delta AS dd

ON (d.DeptID = dd.DeptID)

WHEN MATCHED AND d.Manager dd.Manager OR d.DeptName dd.DeptName

THEN UPDATE SET d.Manager = dd.Manager, d.DeptName = dd.DeptName

WHEN NOT MATCHED THEN

INSERT (DeptID, DeptName, Manager)

VALUES (dd.DeptID, dd.DeptName, dd.Manager)

WHEN NOT MATCHED BY SOURCE THEN

DELETE

OUTPUT $action,

inserted.DeptID AS SourceDeptID, inserted.DeptName AS SourceDeptName,

inserted.Manager AS SourceManager,

deleted.DeptID AS TargetDeptID, deleted.DeptName AS TargetDeptName,

deleted.Manager AS TargetManager;

MERGE的相关操作就介绍到这里，希望大家有时间能够使用一下加深印象，这个在使用上性能会比较好，而且代码量会减少很多。