手机
当前位置:查字典教程网 >编程开发 >mssql数据库 >SQLServer2008的实用小道具 merger使用介绍
SQLServer2008的实用小道具 merger使用介绍
摘要:A.使用MERGE在单个语句中对表执行UPDATE和DELETE操作下面的示例使用MERGE根据SalesOrderDetail表中已处理的...

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

下面的示例使用 MERGE 根据 SalesOrderDetail 表中已处理的订单,每天更新 AdventureWorks 示例数据库中的 ProductInventory 表。通过减去每天对 SalesOrderDetail 表中的每种产品所下的订单数,更新 ProductInventory 表的 Quantity 列。如果某种产品的订单数导致该产品的库存量下降到 0 或更少,则会从 ProductInventory 表中删除该产品对应的行。

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

下面的示例使用 MERGE 以更新或插入行的方式来修改 SalesReason 表。当源表中的 NewName 值与目标表 (SalesReason) 的 Name 列中的值匹配时,就会更新此目标表中的 ReasonType 列。当 NewName 的值不匹配时,就会将源行插入到目标表中。此源表是一个派生表,它使用 Transact-SQL 行构造函数功能指定源表的多个行。有关在派生表中使用行构造函数的详细信息,请参阅 FROM (Transact-SQL)。

C. 将 MERGE 语句的执行结果插入到另一个表中

下例捕获从 MERGE 语句的 OUTPUT 子句返回的数据,并将该数据插入另一个表。MERGE 语句根据在 SalesOrderDetail 表中处理的订单,更新 ProductInventory 表的 Quantity 列。本示例捕获已更新的行,并将这些行插入用于跟踪库存变化的另一个表中

复制代码 代码如下:

USE AdventureWorks;

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 <= 0

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'

复制代码 代码如下:

USE AdventureWorks;

GO

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, inserted.*, deleted.*;

复制代码 代码如下:

USE AdventureWorks;

GO

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, inserted.*, deleted.*;

复制代码 代码如下:

USE AdventureWorks;

GO

CREATE TABLE Production.UpdatedInventory

(ProductID INT NOT NULL, LocationID int, NewQty int, PreviousQty int,

CONSTRAINT PK_Inventory PRIMARY KEY CLUSTERED (ProductID, LocationID));

GO

INSERT INTO Production.UpdatedInventory

SELECT ProductID, LocationID, NewQty, PreviousQty

FROM

( MERGE Production.ProductInventory AS pi

USING (SELECT ProductID, SUM(OrderQty)

FROM Sales.SalesOrderDetail AS sod

JOIN Sales.SalesOrderHeader AS soh

ON sod.SalesOrderID = soh.SalesOrderID

AND soh.OrderDate BETWEEN '20030701' AND '20030731'

GROUP BY ProductID) AS src (ProductID, OrderQty)

ON pi.ProductID = src.ProductID

WHEN MATCHED AND pi.Quantity - src.OrderQty >= 0

THEN UPDATE SET pi.Quantity = pi.Quantity - src.OrderQty

WHEN MATCHED AND pi.Quantity - src.OrderQty <= 0

THEN DELETE

OUTPUT $action, Inserted.ProductID, Inserted.LocationID, Inserted.Quantity AS NewQty, Deleted.Quantity AS PreviousQty)

AS Changes (Action, ProductID, LocationID, NewQty, PreviousQty) WHERE Action = 'UPDATE';

GO

【SQLServer2008的实用小道具 merger使用介绍】相关文章:

SQL Server 中 RAISERROR 的用法详细介绍

SQL Server 数据库安全管理介绍

例说SQLServer2008聚合函数

SQLServer2005 的查询独占模拟

SQLServer APPLY表运算符使用介绍

SQL Server 2005中的外联结用法

SQL Server 2008空间数据使用教程

SQL Server 游标使用

SQL Server 2008报表服务介绍

SQL Server 2005 中使用 Try Catch 处理异常

精品推荐
分类导航