Post reply

Creating table with identity field

  • June 7, 2006 at 8:19 am

    #112504

    I wish to create a temp table with a identity field, with the identity seed based on the ident_current of another table, the issue is that I CANNOT use dynamic sql,

    i am getting errors when I try to set the identity seed to a variable in the create table statement, are there any work arounds?

  • June 7, 2006 at 8:33 am

    #642289

    Geoff,

    You can create the temp table with the default identity seed and then use dbcc checkident('tablename',reseed,new_seed_value)

    CREATE TABLE #mytemp

    (col1 INT IDENTITY)

    DECLARE @new_seed INT

    SET @new_seed = 20

    DBCC CHECKIDENT('#mytemp',reseed,@new_seed)

    Hope that helps,

    Karl
    source control for SQL Server

  • June 7, 2006 at 9:25 am

    #642311

    spot on, thanks

  • June 7, 2006 at 5:45 pm

    #642409

    Or store the required starting value, then create your table with a seed of 0 and add the increment when you SELECT from the table. Is this for use in production/application code?

    Tim Wilkinson

    "If it doesn't work in practice, you're using the wrong theory"
    - Immanuel Kant

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

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