SQL2008中merge用法

 

/*
1.MERGE 语句

在 SQL Server 2008 中，可以使用 MERGE 语句在一条语句中根据与源表联接的结果对目标表执行 INSERT、UPDATE 或 DELETE 操作。如：使用一个语句有条件地在单个目标表中插入或更新行，如果目标表中存在相应行，则更新一个或多个列；否则，会将数据插入新行。使用该语句还可以同步两个表，根据与源数据的差别在目标表中插入、更新或删除行。

MERGE 语法包括如下五个主要子句：

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

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

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

WHEN 子句用于根据 ON 子句的结果指定要执行的操作。

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

*/
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'),

(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'),

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

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

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

GO

SELECT * FROM  Purchases
SELECT * FROM  FactBuyingHabits

 

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.*;

 

SELECT * FROM  FactBuyingHabits

 

 

 

举例

/*
根据A表内容来更新B表，如果A表中BM=B表中的BM，
那么更新B表中的NUM=A.NUM+B.NUM，如果不匹配，那么将A表中的数据插入到B表
*/
SELECT * FROM TA
SELECT * FROM tb

Merge dbo.tb AS target
using(SELECT * FROM TA )AS Source
ON(target.bm=Source.bm)
WHEN matched THEN
UPDATE SET Target.num=Target.num+Source.num
WHEN NOT matched BY Target THEN
INSERT (bm,name,num,rktime)
VALUES (Source.bm,Source.name,Source.num,Source.rktime)
OUTPUT $action,INSERted.*,deleted.*;

SELECT * FROM tb

SELECT * FROM TA