reseeding temporary tables or table data types with identity column

Hi,

I have a indexing problem. I have a sequence that needs to has a index number. I want to use a table data type and have a working sample BUT I cannot reseed the table when needed. How do I do this.

This works only for the first ExitCoilID then I need to RESEED.

Here is my code:

DECLARE

@.EntryCoilCnt AS INT,

@.ExitCoilID AS INT,

@.SubtractedFromEntyCoilCnt AS INT

DECLARE

@.ExitCoilID_NotProcessed TABLE

(ExitCoilID int)

INSERT INTO @.ExitCoilID_NotProcessed

SELECT DISTINCT ExitCoilID

FROM

dbo.TrendEXIT

where

ExitCoilID is not null and

ExitCnt is null

order by

ExitCoilID

DECLARE

@.ExitCoilID_Cnt_Index TABLE

(ExitCoilID int, ExitCnt int IDENTITY (1,1))

IF @.@.ROWCOUNT > 0

BEGIN

DECLARE ExitCoilID_cursor CURSOR FOR

SELECT ExitCoilID FROM @.ExitCoilID_NotProcessed

ORDER BY ExitCoilID

OPEN ExitCoilID_cursor

FETCH NEXT FROM ExitCoilID_cursor

INTO @.ExitCoilID

WHILE @.@.FETCH_STATUS = 0

BEGIN

INSERT INTO @.ExitCoilID_Cnt_Index

SELECT ExitCoilID

FROM dbo.TrendEXIT

WHERE

ExitCoilID = @.ExitCoilID

ORDER BY

EntryCoilID, Cnt

select * from @.ExitCoilID_Cnt_Index

--truncate @.ExitCoilID_Cnt_Index

--DBCC CHECKIDENT ('@.ExitCoilID_Cnt_Index', RESEED, 1)

FETCH NEXT FROM ExitCoilID_cursor

INTO @.ExitCoilID

END

CLOSE ExitCoilID_cursor

DEALLOCATE ExitCoilID_cursor

select * from @.ExitCoilID_Cnt_Index

END --IF @.@.ROWCOUNT <> 0

Is this SQL 2005? Try using ROW_NUMBER() instead of identity.|||

Hi,

Yes I'm using SQL 2005 and I'm new to it. How is ROW_NUMBER() Used? I don't have my onlinbooks with me.

Thanks

|||you can search for it on www.msdn.com.|||

Hi,

This is good when you have 2005. Is there a way to reseed a memory table under 2K ?

Thanks.

|||

Hi,

Look up DBCC CHECKIDENT in the help

DBCC CHECKIDENT ('TableName', RESEED, ReseedValue);

Don

|||

No, there is not method to reseed the local variable table identity value. If you need this functionality, use a local temporary table instead. They are not as convienient but they give you everything you need, and have very favorable scoping that you should be able to get what you need from them.

set nocount on
create table #table (tableId int identity)
insert into #table default values
insert into #table default values
insert into #table default values
insert into #table default values

select *
from #table

truncate table #table

insert into #table default values
insert into #table default values
insert into #table default values
insert into #table default values

select *
from #table

drop table #table

tableId
--
1
2
3
4

tableId
--
1
2
3
4

Otherwise, if variable based tables give you some incredible value, stop using identities and manage number creation manually, like using a table of numbers, or a loop, or just hard coding values if you aren't doing too many (I expect you are, but this is the easiest to code:

set nocount on
declare @.table table (tableId int )
insert into @.table select 1
insert into @.table select 2
insert into @.table select 3
insert into @.table select 4

select *
from @.table

delete from @.table

insert into @.table select 1
insert into @.table select 2
insert into @.table select 3
insert into @.table select 4

select *
from @.table