Is there anyway to reset the identity of a Table V

问题:

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     |        
|___________|_________________|

回答1:

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

回答2:

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.

回答3:

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

回答4:

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  

回答5:

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.

回答6:

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

回答7:

you should truncate your table instead of deleting all rows from it.

but note that truncate will not work for some tables, (listed from MSDN):

You cannot use TRUNCATE TABLE on tables that:

Are referenced by a FOREIGN KEY constraint. (You can truncate a table that has a foreign key that references itself.)

Participate in an indexed view.

Are published by using transactional replication or merge replication.

and added myself:

You cannot truncate a table variable.

syntac of truncate is:

TRUNCATE TABLE 
    [ { database_name .[ schema_name ] . | schema_name . } ]
    table_name
[ ; ]

EDIT: I didn't notice that you are questioning about table variables.

as far as I know there is no way to reset an identity column in a table variable. you can use a temp table instead.

回答8:

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

回答9:

DELETE FROM does not reset identity. TRUNCATE does.

回答10:

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

回答11:

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 

回答12:

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