如何在T-SQL表变量中重新设置标识列? [英] How can I reseed an identity column in a T-SQL table variable?
问题描述
我有一个T-SQL 表变量(不是表),该变量具有自动递增的标识列.我想清除此变量中的所有数据并将身份列值重置为1.该怎么办?
I have a T-SQL table variable (not a table) which has an auto incrementing identity column. I want to clear all data from this variable and reset the identity column value to 1. How can this be done?
推荐答案
如果您使用表变量,则无法执行.如果它是一个表,则可以截断它或使用DBCC CHECKIDENT
.但是,如果必须使用表变量,则必须使用Identity列以外的其他东西.或者,更准确地说,使用表变量中的identity列,但使用ROWNUMBER
:
If you're using a table variable, you can't do it. If it were a table, you could truncate it or use DBCC CHECKIDENT
. But, if you have to use a table variable, you have to use something other than an identity column. Or, more accurately, use the identity column in your table variable but output using ROWNUMBER
:
DECLARE @t table (pkint int IDENTITY(1,1), somevalue nvarchar(50))
INSERT INTO @t (somevalue) VALUES( 'one')
INSERT INTO @t (somevalue) VALUES('twp')
INSERT INTO @t (somevalue) VALUES('three')
SELECT row_number() OVER (ORDER BY pkint), somevalue FROM @t
DELETE FROM @t
INSERT INTO @t (somevalue) VALUES('four')
SELECT row_number() OVER (ORDER BY pkint), somevalue FROM @t
最好使用table变量.
It's the best you can do with the table variable.
这篇关于如何在T-SQL表变量中重新设置标识列?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!