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

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;

 当数据匹配就新增数据，不匹配就删除源数据

Merge INTO dbo.User as Source 
USING (SELECT 1 as PId,N'xx' as Code) As Target on Source.PId=Target.PId and Source.Code=Target.Code
WHEN NOT MATCHED BY Target THEN 
   INSERT (PId,Code) 
   VALUES(Target.PId,Target.Code) output INSERTED.Id
WHEN NOT MATCHED BY Source THEN 
   DELETE;

当数据匹配就新增数据，不匹配就删除指定源数据

Merge INTO dbo.User as Source 
USING (SELECT 1 as PId,N'xx' as Code) As Target on Source.PId=Target.PId and Source.Code=Target.Code
WHEN NOT MATCHED BY Target THEN 
   INSERT (PId,Code) 
   VALUES(Target.PId,Target.Code) output inserted.Id;
WHEN NOT MATCHED BY Source AND Target.Code=N'xx' THEN 
   DELETE;