SQL2008 Merge关键字用法与简例

--------------------------------------------------------------------------

--  Author : htl258(Tony)

--  Date   : 2010-04-25 01:10:28

--  Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)

--          Jul  9 2008 14:43:38

--          Copyright (c) 1988-2008 Microsoft Corporation

--          Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)

--  Blog   : http://blog.csdn.net/htl258

--  Subject: SQL2008 Merge关键字用法与简例

--------------------------------------------------------------------------

--Merge 语法是对表进行插入，更新，删除这三个操作的合并。

--根据与源表联接的结果，对目标表执行插入、更新或删除操作。

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

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

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

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

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

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

 

 

--示例：

 

USE tempdb

GO

--创建表A

IF NOT OBJECT_ID('[A]') IS NULL

    DROP TABLE [A]

GO

CREATE TABLE A(ID INT,ACOL VARCHAR(10))

GO

INSERT A VALUES(1,N'A')

INSERT A VALUES(2,N'B')

INSERT A VALUES(3,N'C')

INSERT A VALUES(6,N'X') --此记录在B表中不存在，删除

GO

--创建表B

IF NOT OBJECT_ID('[B]') IS NULL

    DROP TABLE [B]

GO

CREATE TABLE B(ID INT,BCOL VARCHAR(10),XCOL VARCHAR(10))

GO

INSERT B VALUES(1,N'A',N'T')  --1的ID与A表ID匹配且指定值与A表指定值对应相同，不变

INSERT B VALUES(2,N'P',N'O')  --2和的ID与A表ID匹配，指定值与A表指定值对应不同，更新

INSERT B VALUES(3,N'P',N'N')

INSERT B VALUES(4,N'L',N'Y')  --4和的ID与A表ID匹配，指定值在A表中对应不存在，插入

INSERT B VALUES(5,N'E',N'S') 

GO

--开始合并两个表：

MERGE A --要处理的表

USING B --参照的表

ON A.ID=B.ID --关联条件

WHEN NOT MATCHED THEN INSERT VALUES(B.ID,B.BCOL) --如果要处理表没有参照表上的记录，则插入

WHEN MATCHED THEN UPDATE SET A.ACOL=B.BCOL --如果记录匹配，就更新目标表的匹配行

WHEN NOT MATCHED BY SOURCE THEN DELETE --如果要处理表的记录在参照表上不存在，则删除

OUTPUT $action, Inserted.*, Deleted.*; --相当于输出以上语句的操作记录

 

/*--result:

$action    ID          ACOL       ID          ACOL

---------- ----------- ---------- ----------- ----------

INSERT     4           L          NULL        NULL

INSERT     5           E          NULL        NULL

UPDATE     1           A          1           A

UPDATE     2           P          2           B

UPDATE     3           P          3           C

DELETE     NULL        NULL       6           X

 

(6 行受影响)

*/

--看看A表更新成什么样：

SELECT * FROM A

/*

ID          ACOL

----------- ----------

1           A

2           P

3           P

4           L

5           E

 

(5 行受影响)

*/

--再变换一个字段

MERGE A --要处理的表

USING B --参照的表

ON A.ID=B.ID --关联条件

WHEN NOT MATCHED THEN INSERT VALUES(B.ID,B.XCOL)

WHEN MATCHED THEN UPDATE SET A.ACOL=B.XCOL

WHEN NOT MATCHED BY SOURCE THEN DELETE

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

 

/*--result:

$action    ID          ACOL       ID          ACOL

---------- ----------- ---------- ----------- ----------

UPDATE     1           T          1           A

UPDATE     2           O          2           P

UPDATE     3           N          3           P

UPDATE     4           Y          4           L

UPDATE     5           S          5           E

 

(5 行受影响)

*/

--再看看现在的A表像什么样

SELECT * FROM A

/*

ID          ACOL

----------- ----------

1           T

2           O

3           N

4           Y

5           S

 

(5 行受影响)

*/