how can i use variable in identity seed

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

how can i use variable in identity seed

Author

Topic

js.reddy
Yak Posting Veteran

80 Posts

Posted - 2010-02-24 : 02:52:19

I want to use a variable in identity seed while creating a table

example

DECLARE @number INT;
SET @number = 100;

CREATE TABLE #t1
(ID INT IDENTITY (@number,1),
ITEMNAME VARCHAR(10)
)
GO

The above script is giving error.

Can any one tell me how can i use a variable in identiy seed.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-24 : 02:56:15

quote:
Originally posted by js.reddy

I want to use a variable in identity seed while creating a table

example

DECLARE @number INT;
SET @number = 100;

CREATE TABLE #t1
(ID INT IDENTITY (@number,1),
ITEMNAME VARCHAR(10)
)
GO

The above script is giving error.

Can any one tell me how can i use a variable in identiy seed.

You need a dynamic SQL. Why do you want to do this?

Madhivanan

Failing to plan is Planning to fail

js.reddy
Yak Posting Veteran

80 Posts

Posted - 2010-02-24 : 02:58:22

ok fine. i will go for dynamic query
thanks Madhivanan.

Kristen
Test

22859 Posts

Posted - 2010-02-24 : 05:14:56

Re seed it instead? This will reseed to the current actual highest number, plus 1. But you could just put your @number variable in the DBCC statement


DECLARE @MyID int
SELECT @MyID = COALESCE(MAX(MyID)+1, 10000) FROM dbo.MyTable
DBCC CHECKIDENT ('dbo.MyTable', RESEED, @MyID)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-24 : 05:36:44

I guess OP wants to use the #temp table for some other purpose where reseeding may not work

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-02-24 : 11:35:38

Ah, sorry, I missed that it was a #TEMP table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources