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

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: