Tuesday, March 24, 2009

Insert into an IDENTITY column Table

SET IDENTITY_INSERT MemberMailID ON;
insert into MemberMailID (pkid,inputvalue,insertdate) values(589297975,1,getdate());
SET IDENTITY_INSERT MemberMailID OFF;



create table #tmp (lastid int)

declare @MemberMailID int
insert into #tmp
exec up_PrimaryKey_Select 'MemberMailID'
select @MemberMailID = lastid from #tmp
select @MemberMailID


-------------------------------------

ALTER proc [dbo].[up_PrimaryKey_Select] @KeyName varchar(75),@InputValue int = null
as
set nocount on
declare @ExecString nvarchar (1000)
declare @PKID int
if @InputValue is null
begin
set @InputValue = 1
end
set
set @ExecString = 'insert '+@KeyName+'(InputValue) values(@InputValue)
select @PKID = scope_identity()'
exec sp_executesql @ExecString,
N'@InputValue int,@PKID int output',@InputValue, @PKID = @PKID output
select @PKID as PKID
return

No comments: