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;

表示匹配时更新code，不匹配时就新增数据，匹配与不匹配的情况可选其一，若是希望的执行是不配情况下执行新增，匹配不做处理，就只需要写出不匹配的执行

Merge INTO dbo.User as Sor 
USING (SELECT 1 as PId,N'xx' as Code) As Frm on Sor.PId=Frm.PId and Sor.Code=Frm.Code
WHEN NOT MATCHED THEN 
INSERT (PId,Code) values(Frm.PId,Frm.Code) output inserted.Id;