pass variable to identity function

is it possible to pass a variable to an identity funtion

example

declare @.max_note int

select @.max_note = max(key ) from notes

select m_key = identity( int, @.max_note, 1),
name

into #prod_note

from prod_note

this one challeged me a lot. heres a solution

insert into #temp

delete from #temp

and do insert again

USE NORTHWIND
declare @.cmd nvarchar(4000)
declare @.max_note int
select @.max_note =max(employeeid ) from employees
SELECT IDENTITY(int, 1,1) AS IDNUM,LASTNAME INTO #TEMP FROM EMPLOYEES
--SELECT * FROM #TEMP
DELETE FROM #TEMP
INSERT #TEMP(LASTNAME)
SELECT LASTNAME FROM EMPLOYEES
SELECT * FROM #TEMP

DROP TABLE #TEMP

not the best solution but works the same

|||

Identity function doesn't support variables for the seed or increment. So you have several options since you are creating a temporary table:

1. Create the temporary table without the identity column and then do an ALTER TABLE to add the identity column using dynamic SQL like:

exec('alter table #prod_note add m_key int identity(' + @.max_note_val + ', 1)')

2. Create an empty table using CREATE TABLE and then use DBCC CHECKIDENT (this accepts variable for seed parameter) to reseed the table before doing the insert. This will be fine if you are dumping small number of rows into the temporary table & performance is not the main criteria. The main reason being that SELECT INTO is the fastest way to create a temporary table with data and doing CREATE followed by INSERT...SELECT will be slower.

|||This answer above is awesome!