SQLSERVER merge的简单用法

 MERGE INTO TEST_IN_COPY3 a USING (
SELECT
'武汉-北京998' AS NAME,
'2017 - 11 - 21 11 : 53 : 49.0000000 + 00 : 00' AS datetimeoffseta,
'2017-11-10 11:52:20' AS datetim2e,
998 AS ID,
'2017-11-07 11:52:10' AS datetimea,
'2017-11-10 00:00:00' AS datea,
5.5600 AS numerica,
5.55 AS floata
) b ON (a.ID = b.ID)
WHEN MATCHED THEN
UPDATE
SET a.NAME = b.NAME,
 a.datetimeoffseta = b.datetimeoffseta,
 a.datetim2e = b.datetim2e,
 a.datetimea = b.datetimea,
 a.datea = b.datea,
 a.numerica = b.numerica,
 a.floata = b.floata
WHEN NOT MATCHED THEN
INSERT (
NAME,
datetimeoffseta,
datetim2e,
ID,
datetimea,
datea,
numerica,
floata
)
VALUES
(
b.NAME,
b.datetimeoffseta,
b.datetim2e,
b.ID,
b.datetimea,
b.datea,
b.numerica,
b.floata

) ;

下面是主要的同步操作

MERGE INTO - 数据的目的地，将数据最终 MERGE 到的表对象

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

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

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

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

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;

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

• 在 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 语句查看一下结果，两个表的数据一模一样了 -

ID = 1,2,3 的记录在 Source 表和Target 表都存在，因此执行的是 UPDATE 操作。

ID = 4，5 的记录在 Source 表存在，但是在 Target 表不存在，因此执行的是 INSERT 操作。

ID = 6，7 的记录在 Target 表存在，但是在 Source 表不存在，因此执行的是 DELETE 操作。

22222222222222

使用merge同时执行insert和update操作

我们经常会有这样的需求，根据某个字段或多个字段查找表中的一行或多行数据，如果查找成功得到匹配项，更新其中的其他一个或多个字段；如果查找失败则将“某个字段或多个字段”作为新的一行中的数据插入到表中。第一种方法是先更新，然后根据@@rowcount判断是否有匹配项，如果没有则插入。先使用下面的 代码创建一个存储过程。

  
  

   
    1 
   
   use
   
    AdventureWorks

   
    2 
   
   
   
   go
   
   

   
    3 
   
   
   
   create
   
    
   
   procedure
   
    dbo.InsertUnitMeasure 
   
   @UnitMeasureCode
   
    
   
   nchar
   
   (
   
   3
   
   ),
   
   @Name
   
    
   
   nvarchar
   
   (
   
   25
   
   )

   
    4 
   
   
   
   as
   
   

   
    5 
   
   
   
   begin
   
   

   
    6 
   
   
   
   set
   
    nocount 
   
   on
   
   ;

   
    7 
   
   
   
   update
   
    Production.UnitMeasure 
   
   set
   
    Name
   
   =
   
   @Name
   
    
   
   where
   
    UnitMeasureCode
   
   =
   
   @UnitMeasureCode
   
   

   
    8 
   
   
   
   if
   
   (
   
   @@ROWCOUNT
   
   =
   
   0
   
   )

   
    9 
   
     
   
   begin
   
   

   
   10 
   
     
   
   insert
   
    
   
   into
   
    Production.UnitMeasure(Name,UnitMeasureCode)
   
   values
   
   (
   
   @Name
   
   ,
   
   @UnitMeasureCode
   
   )

   
   11 
   
     
   
   end
   
   

   
   12 
   
   
   
   end
   
   

   
   13 
   
   
   
   go
  
  

记得见过这样的笔试题目，要求是插入不存在的行，只要把上面语句中的update改成select就可以了，当时没有写出来，现在恍然大悟，也许是在考察@@ROWCOUNT的用法吧。这个语句也可以使用merge语句实现。下面我们使用merge关键字来修改这个存储过程。

  
  

   
    1 
   
   alter
   
    
   
   procedure
   
    dbo.InsertUnitMeasure 
   
   @UnitMeasureCode
   
    
   
   nchar
   
   (
   
   3
   
   ),
   
   @Name
   
    
   
   nvarchar
   
   (
   
   25
   
   )

   
    2 
   
   
   
   as
   
   

   
    3 
   
   
   
   begin
   
   

   
    4 
   
   
   
   set
   
    nocount 
   
   on
   
   

   
    5 
   
   merge Production.UnitMeasure 
   
   as
   
    target

   
    6 
   
   using (
   
   select
   
    
   
   @UnitMeasureCode
   
   ,
   
   @Name
   
   ) 
   
   as
   
    source (UnitMeasureCode,Name)

   
    7 
   
   
   
   on
   
    (target.UnitMeasureCode
   
   =
   
   source.UnitMeasureCode)

   
    8 
   
   
   
   when
   
    matched 
   
   then
   
    
   
   update
   
    
   
   set
   
    Name
   
   =
   
   source.Name

   
    9 
   
   
   
   when
   
    
   
   not
   
    matched 
   
   then
   
    
   
   insert
   
   (UnitMeasureCode,Name)
   
   values
   
   (source.UnitMeasureCode,Name)

   
   10 
   
   output deleted.
   
   *
   
   ,$action,inserted.
   
   *
   
    
   
   into
   
    MyTempTable;

   
   11 
   
   
   
   end
   
   

   
   12 
   
   
   
   go
  
  

这个语句使用merge修改存储过程，这个语句中又出现我不太了解的关键字using和$action。Using是用来指定和表InsertUnitMeasure中相匹配的数据源，这里的数据源来自外部输入，是通过两个输入参数得到。$action可能是一个占位符，表示上面的when字句进行的操作