MS SQL 当记录不存在时插入insert INTO not exists

INSERT INTO dbo.[T_DabaoTemp]  ([PType]
           ,[pID]
           ,[NewVersion]
           ,[ParentCode]
           ,[Addtime])
 select 0,5,'2.0.1','0_3',GETDATE() WHERE not exists (
 select 1 from [T_DabaoTemp] where [PType]=0 and [pID]=5 and [NewVersion]='2.0.1')

 

存储过程

USE [UMoneyMain]
GO
/****** Object:  StoredProcedure [dbo].[UMoney_T_DabaoTemp_Insert]    Script Date: 04/09/2014 10:19:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
----------------------------------------------------------------------------------------------------
-- Date Created: 2014年4月8日 16:27
-- Created By:王朝理
-- Purpose: 向表 T_DabaoTemp 中插入数据
-- 表说明
----------------------------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[Temp_Insert]
(
 @Id int output, --  
 @PType int = 0 , --  
 @pID int = 0 , --  
 @NewVersion varchar(50) = null , --  
 @ParentCode varchar(50) = null , --  
 @Addtime datetime = null  --  
)
AS     
SET NOCOUNT ON
 if (@Addtime is null)
 begin
  set @Addtime = getdate()
 end
 INSERT INTO dbo.[T_DabaoTemp]  ([PType]
           ,[pID]
           ,[NewVersion]
           ,[ParentCode]
           ,[Addtime])
 select @PType,@pID,@NewVersion,@ParentCode,@Addtime WHERE not exists (
 select 1 from [T_DabaoTemp] where [PType]=@PType and [pID]=@pID and [NewVersion]=@NewVersion)
  
  
 SET @Id = SCOPE_IDENTITY()

转载于:https://www.cnblogs.com/yeye518/p/3653728.html