Post reply

Local variable as seed in an identity column

  • September 13, 2005 at 9:44 am

    #91082

    Hi all,

    Is there any way that we can specify a local variable as seed in an identity column?

    Iam trying to do like this but getting an error.

     create table #sometable(id1 int IDENTITY(@ct, 1))

    Thanks for any help.

  • September 13, 2005 at 9:50 am

    #589428

    while the table definition doesn't allow variables, the DBCC command to change the starting value does:

    declare @C1 int

    set @c1=99

    create table #sometable(id1 int IDENTITY(1, 1))

    DBCC CHECKIDENT( [#sometable],RESEED,@c1)

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • September 13, 2005 at 9:51 am

    #589430

    You will have to form a string and then execute it and you would also need to use global temp table then rather than using a local temp table (don't know whether you can do this in your application logic as local and global temp tables are very different in their behavior of scope etc. - look up BOL for more information).

    Example of forming a string and then executing it:

    declare @CT varchar(10), @command nvarchar(1000)

    set @CT = 1

    set @command = 'create table ##sometable(id1 int IDENTITY('+@ct+', 1), col2 int)'

    exec sp_executesql @command

    go

    insert into ##sometable (col2) values (1)

    select * from ##sometable

    id1 col2

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

    1 1

  • September 13, 2005 at 9:53 am

    #589431

    And another way is to use DBCC command as shown above and re-seed the value after the table gets created.

  • September 13, 2005 at 10:22 am

    #589441

    It worked me great, Thank you so much Lowell. When the time I was doing I didn't even think of any of the DBCCs.

     

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply