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;
此随笔或为自己所写、或为转载于网络。仅用于个人收集及备忘。