Identity Function question

[ddeegan]

Hello - I can only seem to get identity() to work if I type the # directly into it.

identity(int,1,1) as sequence_no,

instead of...

identity(int,@mynewid,1) as sequence_no,

from the example below

-----------------------------------------------------------
declare
@mynewid int

execute GetNextID 'USR_COMPANY_HISTORY', 'SEQUENCE_NO','DDEEGAN', 1
select @mynewid=next_id from app_next_number
where TABLE_NAME='USR_COMPANY_HISTORY'


select
DCT.CUSTOMER_ID as 'master_customer_id',
'0' as sub_customer_id ,
identity(int,@mynewid,1) as sequence_no,
null as prospect_id
into USR_COMPANY_HISTORY_TEMP
from DCT_CUS_COMPANY_HISTORY DCT

returns

Server: Msg 170, Level 15, State 1, Line 17
Line 17: Incorrect syntax near '@mynewid'.

What am I doing wrong with identity()?

"identity(int,1,1) as sequence_no " works fine - but if I try to replace the second parameter - it don't work

Thanks in advance
Dave

 

[vongrunt]

identity() does not accept @variable for seed.

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[ddeegan]

So the only option is to type the # in there?

I just want to make sure I'm understanding. I was using a cursor and incrementing a # but it took forever to load a table.


Thanks
Dave

 

[vongrunt]

So the only option is to type the # in there

Nope...

1. create target table with sequence_no int identity(1, 1)
2. reseed identity with DBCC CHECKIDENT(targettable, RESEED, @mynewid)
3. do INSERT INTO targettable instead of SELECT INTO

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[ddeegan]

Thanks vongrunt - I am doing steps 1 & 3

I've never seen "DBCC CHECKIDENT"

Awesome - Thank You!