Resetting Identity column of Table Variable

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 2000 Forums

Transact-SQL (2000)

Resetting Identity column of Table Variable

Author

Topic

anandc
Starting Member

20 Posts

Posted - 2006-05-09 : 08:52:42

Hi

I want to Reset the identity column of a Table Variable in my SP. Whenever I am iterating through a loop, for every iteration I am creating a table variable which contains an identity column. I want to reset this column and it should start with value "1" for each iteration.

Below is a sample piece of code...


WHILE i < 5
BEGIN
    DECLARE @mytbl AS TABLE(id INT IDENTITY(1,1), MyName VARCHAR(50))
    -- perform some operations on table variable
    SET i = i + 1
END

Initially the same code was working, and we found out this problem has suddenly started appearing.

Any help in this regard would be appreciated.....

Thanks
Anand

- Anand

sshelper
Posting Yak Master

216 Posts

Posted - 2006-05-09 : 09:48:16

Try truncating the table even after it is declare:

WHILE i < 5
BEGIN
DECLARE @mytbl AS TABLE(id INT IDENTITY(1,1), MyName VARCHAR(50))
TRUNCATE TABLE @mytbl

-- perform some operations on table variable
SET i = i + 1
END

SQL Server Helper
http://www.sql-server-helper.com

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-09 : 09:56:32

>>TRUNCATE TABLE @mytbl
Not supported

Madhivanan

Failing to plan is Planning to fail

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-05-09 : 11:47:10

Hi all,

Anand - I don't think you can 'reseed' a table variable, sadly. You might have to use a temporary table instead. The 'truncate' syntax sshelper gave you should then work.

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources