Sql server使用Merge关键字做插入或更新操作

Merge是关于对于两个表之间的数据进行操作的。

    要使用Merge的场景比如:

•     数据同步
•     数据转换
•     基于源表对目标表做Insert,Update,Delete操作 

MERGE语句的基本语法：

MERGE 目标表

USING 源表

ON 匹配条件

WHEN MATCHED THEN

   语句

WHEN NOT MATCHED THEN

   语句;

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

Merge关键字的一些限制

•     使用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) 
WHEN MATCHED THEN 
    UPDATE SET d.ProductName = s.ProductName, d.Price = s.Price;

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

 

DECLARE @ID int;
DECLARE @isNew as bit = 0;

MERGE CUSTOMER WITH(ROWLOCK) as target
using(select @accountID as AccountID) as source
on target.ACCOUNT_ID = Source.AccountID AND target.IS_DELETE = 0
WHEN MATCHED THEN
    UPDATE set CNAME = @cname
              ,NICKNAME = @nikename
              ,MOBILE = @mobile
              ,CERTIFICATE_TYPE = @certificatetype
              ,CERTIFICATE_CODE = @certificatecode
              ,ADDRESS = @address
              ,PHOTO_URL = @photourl
              ,BIRTHDAY = @birthday
              ,CAREER = @career
              ,GENDER = @gender
              ,HEIGHT = @heigth
              ,WEIGHT = @weight
              ,REGIST_ON = @regison
              ,MODIFIED_ON = GETDATE()
              ,VERSION = target.VERSION + 1              
              ,@ID = target.ID
WHEN NOT MATCHED THEN
    INSERT (CNAME
           ,NICKNAME
           ,MOBILE
           ,CERTIFICATE_TYPE
           ,CERTIFICATE_CODE
           ,ADDRESS
           ,PHOTO_URL
           ,BIRTHDAY
           ,CAREER
           ,GENDER
           ,HEIGHT
           ,WEIGHT
           ,REGIST_ON
           ,ACCOUNT_ID
           ,IS_DELETE
           ,CREATED_ON
           ,MODIFIED_ON
           ,VERSION
           ,GUID
           ,SYSTEM_TYPE
           ,PRIORITY)
    values(@cname
          ,@nikename
          ,@mobile
          ,@certificatetype
          ,@certificatecode
          ,@address
          ,@photourl
          ,@birthday
          ,@career
          ,@gender
          ,@heigth
          ,@weight
          ,@regison
          ,@accountID
          ,0
          ,GETDATE()
          ,GETDATE()
          ,1
          ,NEWID()
          ,0
          ,0);

if @ID is NULL 
begin
set @ID = SCOPE_IDENTITY()
set @isNew = 1;
end;
SELECT @ID as ID,@isNew as IsNew;