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 targetusing(select @accountID as AccountID) as sourceon target.ACCOUNT_ID = Source.AccountID AND target.IS_DELETE = 0WHEN 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.IDWHEN 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 beginset @ID = SCOPE_IDENTITY()set @isNew = 1;end;SELECT @ID as ID,@isNew as IsNew;