Is there anyway to reset the identity of a Table Variable?

Question

Say I have a table variable:

DECLARE @MyTableVar TABLE (ID INT IDENTITY(1,1), SomeData NVARCHAR(300))

After I have inserted 250 rows, I need to "Start Over" with the table. I do this:

DELETE FROM @MyTableVar

Is there anything I can do to the table variable so that this:

insert into @MyTableVar Values("TestData")
select * from @MyTableVar

will return this:

_______________________________
|    ID     |    SomeData     |
|___________|_________________|
|           |                 |   
|     1     |    TestData     |        
|___________|_________________|

instead of this:

_______________________________
|    ID     |    SomeData     |
|___________|_________________|
|           |                 |   
|    251    |    TestData     |        
|___________|_________________|

possible duplicate http://stackoverflow.com/questions/147210/how-can-i-reseed-an-identity-column-in-a-t-sql-table-variable — Binil, Jun 24 '11 at 17:33

score 17 · Answer 1 · answered Jun 24 '11 at 17:12

17

Instead relying on an Identity, why not use the new ranking functions such as Row_Number

Insert @MyTableVar( Id, Value )
Select Row_Number() Over ( Order By Value )
    , Value
From SomeOtherTable

answered Jun 24 '11 at 17:12

Thomas

63,911
12
95
141

This is close to working, but it re-orders my result set (or at least in my example it does). The order that I insert thing is very very important in this case. – Vaccano Jun 24 '11 at 18:48
@Vaccano - The order that I showed was for example purposes only. You can obviously change it whatever you wish. The point was to illustrate the approach rather than a specific solution. We're never told in your question what order you were using. – Thomas Jun 24 '11 at 19:59
@Vaccano - One more item, if the order is important, then presumably you are applying an Order By when you insert into your table var with an identity. That same order would be used in the ranking function. – Thomas Jun 24 '11 at 22:20
Alas, I am getting my data from an XML variable (Using XQuery) so I don't have any order by or strong Id to use. It just comes out in the order it is in the file (and the order I need it in). – Vaccano Jun 24 '11 at 22:25
@Vaccano - First, if that's the case, then that's a critical piece to your problem. You should add that to the question. There may still be a way to dictate an order. Second, I'm not sure I would rely on the fact that the order as entered into the table exactly matched the physical order in the Xml snippet without explicitly stating as such. I doubt that's a supported "feature" on which you can depend and which will not change under any condition or in future versions. – Thomas Jun 25 '11 at 04:31
I had a similar situation, but the example in http://stackoverflow.com/questions/147210/how-can-i-reseed-an-identity-column-in-a-t-sql-table-variable showed how I could use my identity column to retain the ordering of the data, and still "reset" my numbers back to 1 each time by using "row_number() OVER (ORDER BY )" – Daniel Bragg Apr 06 '15 at 22:33

score 6 · Answer 2 · answered Jun 24 '11 at 19:51

6

Instead of re-seeding the IDENTITY, why not just delete from the @table variable, then use ROW_NUMBER() against the input? e.g. instead of the lazy

SELECT * FROM @MyTableVar;

...use...

SELECT ID = ROW_NUMBER() OVER (ORDER BY ID), SomeData FROM @MyTableVar;

Now you don't need to care what the seed is, whether it starts at 1, whether there are any gaps, etc.

answered Jun 24 '11 at 19:51

Aaron Bertrand

272,866
37
466
490

1

Just to clarify, this seems similar but is different from the suggestion from Thomas, as he suggested trying to insert the data with a ROW_NUMBER() applied first. If there are multiple insert statements happening independently, applying a ROW_NUMBER() first won't work, especially because there may not be an obvious order within the INSERT statement itself. – Aaron Bertrand Jun 24 '11 at 20:04
If there are multiple inserts happening, the identity approach would also break would it not? You might get gaps or an order that is unexpected (e.g. 1st insert A,B,C which get identity values of 1,2,3. 2nd insert A,B,D which get 4,5,6 but not the same as the sequence). It really comes down to when the OP needs the sequence and I get the impression they need a sequence multiple times thus the question about how to reseed the identity. – Thomas Jun 24 '11 at 22:18
@Thomas, if the op has ensured that the IDENTITY values that get assigned occur in the correct order (I assume he is inserting rows one by one, and that the "order" is dictated by something that is not already in the table) then this approach should work fine. The only problem the op was having is that the values he was getting on subsequent queries from the table were not starting at 1. If he keeps the IDENTITY column he can still pull the data ordered by insert but use ROW_NUMBER() to generate a 1,2,3 sequence regardless of lowest value, gaps, etc. – Aaron Bertrand Jun 24 '11 at 22:22

score 4 · Answer 3 · answered May 13 '15 at 02:38

unfortunately there is no function to reseed identity column in table variable, I know this question is very old, but in case other people encountered the same problem, I would like to share my method to solve this problem.

/* declare another table variable with same structure and perform select insert*/

DECLARE @MyTableVar1 TABLE (ID INT IDENTITY(1,1), SomeData NVARCHAR(300))
insert into @MyTableVar1
select someData from @MyTableVar

However, If you want to perform dynamic reseeding inside a loop, I would suggest using a table object

score 2 · Answer 4 · edited Sep 16 '13 at 00:51

2

You can't reseed the identity value on a Table Variable but you can do the same thing with a Temp Table:

CREATE  TABLE #TAB(ID INT IDENTITY,VALUE VARCHAR(10))
DECLARE @RESEED INT = 32
DBCC CHECKIDENT(#TAB,RESEED,@RESEED)
INSERT INTO #TAB  
SELECT 'TEST'
SELECT * FROM #TAB

edited Sep 16 '13 at 00:51

Dan Is Fiddling By Firelight

5,981
17
77
128

answered Sep 16 '13 at 00:25

Venkat

21
1

Christian Soliven · Answer 5 · 2015-08-24T02:30:14.843

1

Since you are re-using your table, if I got it right, how about you do not initialize your counters to 1 and instead use this as an example?

DECLARE @ctr INT
IF @ctr IS NULL or @ctr <= 0 --this part is to control @ctr value on loops
   SET @ctr = 1
ELSE
   SELECT @ctr = MIN(id) FROM @tbl

This way, you are not restarting your loop to 1 nor is there a need for you to truncate the table.

edited Aug 24 '15 at 02:30

answered Aug 05 '15 at 00:30

Christian Soliven

33
1
6

score 0 · Answer 6 · answered Aug 30 '12 at 11:17

I tried it on net but i am not able to get any solution on reset identity for table variable.

If you are able to use temp table #MyTableVar instead of table @MyTableVar variable then it is possible to reset identity value

   DBCC CHECKIDENT('TableName', RESEED, NewValue)

   DBCC CHECKIDENT(#MyTableVar, RESEED, 0)

Newvalue must be one less than the newIdentiyValue

   NewValue= NewIdentity-1;

If you still want to learn more you can refer my blog http://tryconcepts.blogspot.in/2012/08/reset-identity-column-to-new-id.html

Noel Paricollo · Answer 7 · 2016-10-19T22:28:20.743

I just had this idea and it works!!! :

declare @TableVariable table (
    IdentityColumn int identity(1,1),
    SomeOtherValue int,
    DesiredResult int
)
declare @FirstIdentityValueEachTimeYouLoadDataToTable int
declare @Count int
set @Count = 1

while @Count <= 5
begin
    delete @TableVariable

    insert into @TableVariable (SomeOtherValue) select 45
    insert into @TableVariable (SomeOtherValue) select 90
    insert into @TableVariable (SomeOtherValue) select 2

    select @FirstIdentityValueEachTimeYouLoadDataToTable = min(IdentityColumn) from @TableVariable

    Update @TableVariable set DesiredResult = IdentityColumn - @FirstIdentityValueEachTimeYouLoadDataToTable + 1

    select * from @TableVariable
    set @Count = @Count + 1
end

AhmedHuq · Answer 8 · 2011-06-25T17:53:17.853

0

Is it possible to have another int column on your table variable and update that column with modulo after the insert is finished?

declare @Mytablevar table
(
id int identity(1,1)
,id1 int
somedata nvarchar(300)
)

-- insert your data as you would.  After insert is finished, do the following:

update @mytablevar set id1 = case when id > 250 then id % 250 else id end

edited Jun 25 '11 at 17:53

answered Jun 25 '11 at 16:38

AhmedHuq

459
1
4
13

score -1 · Answer 9 · answered Jun 24 '11 at 17:23

Can you use temporary table? This is a sample how to do this with a temp table.

CREATE TABLE #MyTableVar (ID INT IDENTITY(1,1), SomeData NVARCHAR(300))

insert #MyTableVar(SomeData) values ('test1'), ('test2')

---doesn't work
DELETE FROM #MyTableVar 

insert #MyTableVar(SomeData) values ('test3'), ('test4')
select * from #MyTableVar 

--resets the identity
truncate table #MyTableVar
insert #MyTableVar(SomeData) values ('test3'), ('test4')
select * from #MyTableVar

Regards

Piotr

score -1 · Answer 10 · answered Jun 26 '11 at 17:41

-1

DELETE FROM does not reset identity. TRUNCATE does.

answered Jun 26 '11 at 17:41

LadyRoot

81
1
6

score -2 · Answer 11 · answered Dec 07 '12 at 05:36

If you are using SQL Server then use this DBCC CHECKIDENT('Customer', RESEED, 0) Where Customer is a table name. When you insert records into table after this command your primery key column value will be start from 1 again.

Read this http://codedotnets.blogspot.in/2012/09/how-to-reset-identity-in-sql-server.html

Is there anyway to reset the identity of a Table Variable?

11 Answers11

Linked

Related