Sql 数据存在就返回匹配数据 不存在就新增数据

本次样例的执行内容是，匹配就返回匹配数据的id，不匹配就新增数据

方式一

 DECLARE @userId INT
 if not EXISTS(select 1 from dbo.User where PId=4 AND Code='XX')
 BEGIN
  INSERT INTO dbo.User(PId,Code) VALUES(4,'XX');
  SELECT @userId=IDENT_CURRENT('User');
 END
 ELSE
  BEGIN SELECT  @userId= id from dbo.User where PId=4 AND PlatCode='XX' 
 END
select @userId;

 

方式二

Merge into dbo.User as Sor using (SELECT 4 as PId,N'XX' as Code) As Frm on Sor.PId=Frm.PId and Sor.Code=Frm.Code
WHEN MATCHED THEN
UPDATE set Sor.Code=Sor.Code
WHEN NOT MATCHED 
THEN INSERT (PId,Code) values(Frm.PId,Frm.Code) 
output inserted.Id;

注：这种方式是在匹配时强制执行了一次无效的更新，以此来返回匹配数据的id，不太友好，推荐使用方式一