Q

Command to seed identity column in a SQL Server table

Take a look at this command using a variable to seed an identify 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'.

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 last published in December 2007

Dig Deeper on SQL-Transact SQL (T-SQL)

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

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

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close