August 4, 2004 at 3:22 am
Hi,
How can i reset an identity column declared in a variable of data type 'Table'.
Declare @table1 table
(
a int identity(1,1) not null,
b int
)
Is there any way to reset this identity column 'a' ??
Regards,
Hatim Ali.
August 4, 2004 at 5:59 am
sorry - i had a big reply written to you, then hit preview and it all got deleted, so quick summary here.
with table variable, not possible to truncate table so not sure, but easy to way to circumvent problem that people have been doing for years...
create table identifiertable
(identifier int)
insert identifiertable
select 1
-- in your procedure do this whenever a new id needed
declare @id
update identifiertable
set @id = identifier = identifier + 1
insert @table1
select @id, 'b'
Sorry, original message was more verbose, but this system deleted my message.
Oh yeah, obviously whenever you want to reset your identity value with above system you use:
update identifiertable
set @id = identifier = 1
If you are going to go around resetting this value though you should make sure you don't use this for more than one tables identifier column. You will need an identifiertable for each table you wish to have this kind of identifier control.
Dave Hilditch.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply