Ask the Expert

Command to seed identity column in a SQL Server table

I would like to use a variable to seed an identity column in a table. I've tried the following, but it gives me an error.

Declare
@last_diary_id int,
@initial_seed int
Select @last_diary_id = 17000 -- this actualy happens else wher in the code
Select @initial_seed = @last_diary_id + 1 Select @initial_seed Create Table #ClaimNoDiary
(table_id int IDENTITY(@initial_seed ,1)
PRIMARY KEY,
adjuster varchar(50),
account varchar(50))

The error I get is:

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

    Requires Free Membership to View

    Login

I don't think you can use a variable to specify the seed when creating a table. Luckily, you can use the DBCC CHECHKIDENT command to modify the seed and this command does allow you to use a variable. Try running something like this after the table has been created:

DBCC CHECKIDENT ('#ClaimNoDiary', RESEED, @initial_seed);

This was first published in December 2007

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top