--Your Stored Procedure 
CREATE PROCEDURE [dbo].[getnextBooknumber] 
(
	@CounterType  varchar(10)
)
AS
Begin 
	 SET NOCOUNT ON
	 Declare @CounterValue As Float
	 Select @CounterValue = IsNull(Max(CounterValue),0) +1 From Counter Where CounterType = @CounterType
	  If @CounterValue = 1 
		 Insert Into Counter (CounterType, CounterValue) Values (@CounterType, @CounterValue)
	  Else 
		 Update Counter Set CounterValue = @CounterValue Where CounterType = @CounterType
		
	 SET NOCOUNT OFF
	 Select CounterValue = @CounterValue

End
GO
--Your Function 
Create Function [Dbo].GetNextBookNum
(
	@CounterType Varchar(10)
)
As 
Begin 
 Declare @CounterValue As Float
 Select @CounterValue = IsNull(Max(CounterValue),0) +1 From Counter Where CounterType = @CounterType
 Return @CounterValue
End 

--Your Insert Statement
Insert into TestJobTable (BookNumber,BookCode,IsActive,SourceId,ContactNumber)
select Dbo.GETNextBookNum('Book'),BookCode,'1', @SourceId ,Dbo.GETNextBookNum('contact') from TestBookTableTemp
where SourceId = @SourceId
and BookCode not in
	(
		select BookCode
		from TestBookTable
		where SourceId = @SourceId 
	)

quote:

---

Originally posted by afrika

Chirag, its xmas !!! Remember

Take a break. Brilliant work

---

 CREATE PROCEDURE dbo.GetNextBookNumber
/****************************************************************************** 
 Purpose:
 This store procedure is used to get the "Next Book Number" from the Counter
 table for any given type that exists and will create a new type if it doesn't
 already exist.

 If only the CounterType is provided, the Increment will default to "1" which
 will allow other procs currently calling this proc to continue to do so 
 without change.

 If an "Increment" is provided, the CounterValue for the matching CounterType
 will be incrementd by that amount.  

 In either case, @NextBookNumber will return a single value which will be the
 desired CounterValue and the next increment of values will be "reserved".

 WARNING:  NEVER USE THIS PROC IN A DECLARED TRANSACTION.  THE RESULTS OF THIS
           PROC MUST NEVER BE ALLOWED TO ROLLBACK OR DUPLICATE COUNTER VALUES
           MAY RESULT ALONG WITH A WAD OF DEADLOCKS TO BOOT!

 Example Usages:
 1. For currently existing procs... do as before (whatever that was)

 2. For new and rewritten procs that will use 1 or more values

 EXEC dbo.GetNextBookNumber countertype, increment, @NextBookNumber OUT

 @NextBookNumber (must be previously declared) will contain the lowest 
 counter value in the requested "increment" of numbers.

 Revision History:
 Rev 00 - 12/31/2006 - Jeff Moden - Initial creation
******************************************************************************/
--===== Declare parameters
        @CounterType    VARCHAR(10),
        @Increment = 1  FLOAT,
        @NextBookNumber FLOAT OUTPUT
     AS
    SET NOCOUNT ON

--===== If the CounterType does not already exist, create a new entry
     -- for it in the counter table (I think this is very dangerous and it
     -- should be removed)
     IF NOT EXISTS (SELECT 1 FROM Counter WHERE CounterType = @CounterType)
 INSERT INTO dbo.Counter (CounterType) VALUES (@CounterType)

--===== Update the CounterValue and get the return all at the same time so
     -- we don't need a transaction that will certainly cause deadlocks on
     -- this "hotspot" table.
 UPDATE dbo.Counter
    SET @NextBookNumber = CounterValue = ISNULL(CounterValue,1) + @Increment
  WHERE @CounterType = @CounterType

--===== Subtract the increment from the number we just updated to get the 
     -- actual next available CounterValue
    SET @NextBookNumber = @NextBookNumber - @Increment

 RETURN

EXEC dbo.GetNextBookNumber 'BookCode', @Increment, @NextBookNumber OUT

 INSERT INTO sometable
        (Bookcode, othercolumn, othercolumn, othercolumn, etc...)
 SELECT RowNum + @NextBookNumber - 1 AS Bookcode,
        ... other column AS column ...
        ... other column AS column ...
        ... other column AS column ...
        ... etc...
   FROM #Temp