Add a row at a specified position in a identity column

				
--**************************************
-- Name: Add a row at a specified position in a identity column
-- Description:Add a row at a specified position in a identity column
-- By: Developer142
--
--This code is copyrighted and has-- limited warranties.Please see http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=1254&lngWId=5--for details.--**************************************

ALTER PROCEDURE [dbo].[USP_AddRow] 
	-- Add the parameters for the stored procedure here
	(@idReceiptNo as int)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
-- Insert statements for procedure here
	Declare @cnt as int
	Declare @cnt1 as int	
	Declare @ReceiptNo as int
	Declare @idcast as int
	Declare @duedate as datetime
	Declare @installment as int
	Declare @emiAmt as float
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	--Fetching the total number of rows from the table
	select @cnt=count(*) from tblInstallments
	
	--Fetching the rows from the table tblInstallments where rownumber between 1st to the @idReceiptNo
	select * into #temp1 from tblInstallments where idReceiptNo between 100 and @idReceiptNo
	select @cnt1=count(*) from #temp1
	set @cnt=@idReceiptNo + @cnt - @cnt1
	print @cnt
	--Fetching the rows from the table tblInstallments where rownumber = target row
	select * into #temp2 from tblInstallments where idReceiptNo = @idReceiptNo
	
	--Fetching the rows from the table tblInstallments where rownumber between the one after the target row and the last
	select * into #temp3 from tblInstallments where idReceiptNo between @idReceiptNo+1 and @cnt
-- Insert statements for procedure here
	
	
	--Fetch the idcast, duedate, installment, emiAmt from the #temp2 table
	select @ReceiptNo= ReceiptNo from #temp2	
	select @idcast= idCust from #temp2
	select @duedate= dueDate from #temp2
	select @installment= installments from #temp2
	select @emiAmt= emiAmt from #temp2
	--select * from #temp2
	--Set Identity_Insert dbo.tblInstallments ON
	--Truncate the original table
	truncate table tblInstallments
	--Insert new data into the original table	
	insert into tblInstallments select ReceiptNo,idCust,dueDate,paidDate,installments,emiAmt,emiAmtPaid,latePayCharge,noOflatePayMonths,outStDays from #temp1 
	insert into tblInstallments(ReceiptNo,idCust,dueDate,paidDate,installments,emiAmt,emiAmtPaid,latePayCharge,noOflatePayMonths,outStDays) values(@ReceiptNo,@idcast,@duedate,null,@installment,@emiAmt,'','','','') 
	insert into tblInstallments select ReceiptNo,idCust,dueDate,paidDate,installments,emiAmt,emiAmtPaid,latePayCharge,noOflatePayMonths,outStDays from #temp3 
	select * from tblInstallments
	-- finally drop the temporary tables
	drop table #temp1
	drop table #temp2
	drop table #temp3