1

Possible Duplicates:
How can I reseed an identity column in a T-SQL table variable?
Is there anyway to reset the identity of a Table Variable?

Please go through the below sample and clarify my Question ?

Declare @temp table (sno int identity, myname varchar(30))
insert into @temp (myname) values ('Ravi')
insert into @temp (myname) values ('visu')
insert into @temp (myname) values ('pranesh')

delete from @temp

insert into @temp (myname) values ('Ravi')

select * from @temp

I am inserting 3 values in @temp table which has auto increment. And later deleting the Value. Immediately adding another value. In this the Auto increment value id jumped to 4. I need to clear the auto increment value with out using dbcc statement.

Can anyone help me ? Your suggestions are highly appreciated. Thank You.

Community
  • 1
  • 1
salaiviswa
  • 113
  • 2
  • 6
  • 1
    Please see this question from last week: http://stackoverflow.com/questions/6471210/is-there-anyway-to-reset-the-identity-of-a-table-variable/ – Aaron Bertrand Jun 30 '11 at 13:23
  • 2
    If you need either of the following with an identity column, you're doing something wrong: requiring that it starts at a particular value; requiring no gaps between values. You should treat them as magical, opaque values, with no inherent meaning. – Damien_The_Unbeliever Jun 30 '11 at 13:37
  • 1
    +1 Damien - it's a unique ID for the row, that's it – JNK Jun 30 '11 at 13:49

1 Answers1

2

You cannot insert explicit values into an identity column for table variable

Pankaj
  • 9,749
  • 32
  • 139
  • 283