MSSQL Server 2008中的MERGE

同步操作

核心提示:就像标题呈现的一样,SQL Server
2008中的MERGE语句能做很多事情

MERGE
INTO – 数据的目的地,将数据最终 MERGE 到的表对象

就像标题呈现的一样,SQL Server
2008中的MERGE语句能做很多事情,它的功能是根据源表对目标表执行插入、更新或删除操作。最典型的应用就是进行两个表的同步。

USING
与源表连接 ON 关联的条件

下面通过一个简单示例来演示MERGE语句的使用方法,假设数据库中有两个表Product及ProductNew,我们的任务是将Product的数据同步到ProductNew。

WHEN
MATCHED – 如果匹配成功,即关联条件成功 (这时就应该将 SOURCE
中其它的所有字段值更新到 TARGET 表中)

以下SQL创建示例表:

WHEN
NOTMATCHED BY TARGET – 如果匹配不成功 (TARGET 中没有这一条记录但是
SOURCE 表有,说明 SOURCE 表多了新数据因此应该插入到 TARGET 表中)

–源表 CREATE TABLE Product ( ProductID varchar(7) NOT NULL PRIMARY KEY,
ProductName varchar(100) NOT NULL, Price decimal(13,2) DEFAULT 0 );

WHEN
NOTMATCHED BY SOURCE – 如果匹配不成功 (SOURCE 中没有这一条记录但是
TARGET 表有,说明 SOURCE 表可能把这条数据删除了,所以 TARGET
也应该删除)

INSERT INTO Product Values (4100037,优盘,50), (4100038,鼠标,30);

MERGE INTO @TargetTable AS T           
USING @SourceTable AS S ON T.ID = S.ID WHEN MATCHED THEN UPDATE SET T.DSPT = S.DSPT WHEN NOT MATCHED BY TARGET THEN INSERT VALUES(S.ID,S.DSPT) WHEN NOT MATCHED BY SOURCE THEN DELETE OUTPUT $ACTION AS [ACTION],
   Deleted.ID AS 'Deleted ID',
   Deleted.DSPT AS 'Deleted Description',
   Inserted.ID AS 'Inserted ID',
   Inserted.DSPT AS 'Inserted Description' INTO @Log;

–目标表 CREATE TABLE ProductNew ( ProductID varchar(7) NOT NULL PRIMARY
KEY, ProductName varchar(100) NOT NULL, Price decimal(13,2) DEFAULT 0 );

还要注意的是有一些限制条件:

下面再来关注MERGE语句的基本语法:


  • Merge Matched 操作中,只能允许执行 UPDATE 或者 DELETE
    语句。

  • Merge Not Matched 操作中,只允许执行 INSERT 语句。
  • 一个
    Merge 语句中出现的 Matched 操作,只能出现一次 UPDATE 或者 DELETE
    语句,否则就会出现下面的错误 – An action of type ‘WHEN MATCHED’ cannot
    appear more than once in a ‘UPDATE’ clause of a MERGE
    statement.
  • Merge
    语句最后必须包含分号,以 ; 结束。

MERGE 目标表


USING 源表

例子:

ON 匹配条件

–源表

WHEN MATCHED THEN

CREATE TABLE test_from (id INT, val VARCHAR(20));
— 目标表
CREATE TABLE test_to (id INT, val VARCHAR(20));
— 插入源表
INSERT INTO test_from VALUES (1, ‘A’);
INSERT INTO test_from VALUES (2, ‘B’);
— 合并 源表到目标表

语句

MERGE test_to 

WHEN NOT MATCHED THEN

USING test_from

语句;

ON ( test_to.id = test_from.id )    — 条件是 id 相同
澳门新葡亰游戏网址,WHEN MATCHED THEN UPDATE SET test_to.val = test_from.val   — 匹配的时候,更新
WHEN NOT MATCHED THEN INSERT VALUES(test_from.id, test_from.val) — 源表有,目标表没有,插入
WHEN NOT MATCHED BY SOURCE THEN DELETE; — 目标表有,源表没有,目标表该数据删除.

以上是MERGE的最最基本的语法,语句执行时根据匹配条件的结果,如果在目标表中找到匹配记录则执行WHEN
MATCHED THEN后面的语句,如果没有找到匹配记录则执行WHEN NOT MATCHED
THEN后面的语句。注意源表可以是表,也可以是一个子查询语句。

— 第一次检查 目标表数据.

格外强调一点,MERGE语句最后的分号是不能省略的!

SELECT * FROM test_to;

回到我们的示例,显然Product与ProductNew表的MERGE匹配条件为主键ProductID字段,初始情况下,ProductNew表为空,此时肯定执行的是WHEN
NOT MATCHED THEN后的语句,我们先只考虑源表递增的情况,MERGE语句如下:

 

MERGE ProductNew AS d USING Product AS s ON s.ProductID = d.ProductId
WHEN NOT MATCHED THEN INSERT( ProductID,ProductName,Price)
VALUES(s.ProductID,s.ProductName,s.Price);

id          val

运行后2行受影响,我们已经将Product表的数据同步到了ProductNew表。

———– ——————–

现在,我们更新Product表4100037产品的价格,将其修改为55:

          1 A

UPDATE Product SET Price=55 WHERE ProductID=4100037;

          2 B

我们也希望每天同步的时候应该将更新后的价格同步到ProductNew表,显然此时在MERGE语句中应该添加WHEN
MATCHED THEN
语句,该语句来更新ProductNew表的价格,添加匹配更新后的MERGE语句:

网站地图xml地图