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?
我有一个T-SQL表变量(不是表)，它有一个自动递增的标识列。我要清除此变量的所有数据，并将identity列值重置为1。这是怎么做到的呢?
6 个解决方案
#1
33
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:
如果您使用的是表变量，那么您就不能这样做。如果是表，可以截断它或使用DBCC CHECKIDENT。但是，如果必须使用表变量，则必须使用标识列之外的其他内容。或者，更准确地说，使用表变量中的标识列，但是使用行号输出:
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
It's the best you can do with the table variable.
这是对表变量所能做的最好的处理。
#2
34
Truncating the table will dump ALL the data, and reset the identity seed.
截断表将转储所有数据，并重置标识种子。
Otherwise, you can use this call to reset the identity while retaining any of the data:
否则，您可以使用此调用来重置标识，同时保留任何数据:
DBCC CHECKIDENT (yourtableName, reseed, @NewStartSeedValue)
#3
0
declare @tb table (recid int,lineof int identity(1,1))
insert into @tb(recid)
select recid from tabledata
delete from @tb where lineof>(select min(lineof) from @tb)+@maxlimit
I did this when I wanted to use a TOP and a variable when using SQL 2000. Basically, you add in the records and then look at the minimum one. I had the same problem and noticed this thread. Deleting the table doesn't reset the seed although I imagine using GO should drop the table and variable to reset the seed.
当我想在使用SQL 2000时使用TOP和变量时，我就这样做了。基本上，你添加记录，然后看最小的记录。我遇到了同样的问题，注意到了这条线。删除表不会重置种子，尽管我认为使用GO应该删除表和变量来重置种子。
@maxlimit in the query above was to get the top 900 of the query and since the table variable would have a different starting identity key, this would solve that issue.
上面查询中的@maxlimit是获取查询的前900，由于表变量具有不同的起始标识键，这将解决这个问题。
Any subsequent query can subtract that derived procedure to make it insert as "1", etc.
任何后续的查询都可以减去派生的过程，使其插入为“1”等。
Hope it helps.
希望它可以帮助。
Lauren
劳伦
#4
0
I suggest you use two table variables. The @Table1 has an identity seed on the first column. @Table2 has the same first column but no identity seed on it.
我建议您使用两个表变量。@Table1在第一列有一个标识种子。@Table2有相同的第一列，但是没有标识种子。
As you loop through your process,
当你循环你的过程，
Insert into @Table2 from @Table1
then Delete From both Tables as your Process Loops.
然后从两个表中删除进程循环。
On your first pass, the @Table2 will have a a sequential number in the first row starting at 1.
在第一次遍历时，@Table2将在第一行中有一个从1开始的序号。
The second time through the loop your second table might have sequential numbers in the first column starting at say 1081. But if you select the minimum value to a variable
第二次循环时，您的第二个表可能在第一列(从1081开始)中有序号。但是如果你选择一个变量的最小值
(Select @FixSeed = min(RowID) From @Table2)
Then you can update @Table2 to make RowID start at 1 as follows:
然后可以更新@Table2，让RowID从1开始，如下所示:
Update @Table2 Set RowID = RowID - @FixSeed +1
Hope this helps
希望这有助于
#5
0
If you have deleted all the rows in a table, and you want to reset the identity column value.
如果删除了表中的所有行，并希望重置identity列值。
USe DBCC CHECKIDENT command.
使用DBCC CHECKIDENT命令。
DBCC CHECKIDENT(tblPerson, RESEED, 0)
This command will reset PersonId identity column.
此命令将重置PersonId标识列。
Another thing to note is that you may need to enclose the table name in single quotes or square brackets if you are referencing by a full path, or if your table name has spaces in it.
需要注意的另一件事是，如果引用的是完整路径，或者表名中有空格，那么可能需要将表名括在单引号或方括号中。
DBCC CHECKIDENT ( ‘databasename.dbo.orders’,RESEED, 0)
#6
-3
If you need to truncate the table variable in each turn of a while loop, you can put the declare @myTbl (...) statement in the loop. This will recreate the table and reset the identity column on each turn of the loop. However, it has a heavy performance hit. I had fairly tight loop, and redeclaring the table variable relative to delete @myTbl was several times slower.
如果需要在while循环的每个回合中截断表变量，则可以将声明@myTbl(…)语句放入循环中。这将重新创建表，并在每次循环中重置标识列。然而，它的性能却很糟糕。我有相当紧密的循环，并且相对于删除@myTbl重新声明表变量的速度要慢几倍。
- Dan
- 丹
#1
33
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:
如果您使用的是表变量，那么您就不能这样做。如果是表，可以截断它或使用DBCC CHECKIDENT。但是，如果必须使用表变量，则必须使用标识列之外的其他内容。或者，更准确地说，使用表变量中的标识列，但是使用行号输出:
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
It's the best you can do with the table variable.
这是对表变量所能做的最好的处理。
#2
34
Truncating the table will dump ALL the data, and reset the identity seed.
截断表将转储所有数据，并重置标识种子。
Otherwise, you can use this call to reset the identity while retaining any of the data:
否则，您可以使用此调用来重置标识，同时保留任何数据:
DBCC CHECKIDENT (yourtableName, reseed, @NewStartSeedValue)
#3
0
declare @tb table (recid int,lineof int identity(1,1))
insert into @tb(recid)
select recid from tabledata
delete from @tb where lineof>(select min(lineof) from @tb)+@maxlimit
I did this when I wanted to use a TOP and a variable when using SQL 2000. Basically, you add in the records and then look at the minimum one. I had the same problem and noticed this thread. Deleting the table doesn't reset the seed although I imagine using GO should drop the table and variable to reset the seed.
当我想在使用SQL 2000时使用TOP和变量时，我就这样做了。基本上，你添加记录，然后看最小的记录。我遇到了同样的问题，注意到了这条线。删除表不会重置种子，尽管我认为使用GO应该删除表和变量来重置种子。
@maxlimit in the query above was to get the top 900 of the query and since the table variable would have a different starting identity key, this would solve that issue.
上面查询中的@maxlimit是获取查询的前900，由于表变量具有不同的起始标识键，这将解决这个问题。
Any subsequent query can subtract that derived procedure to make it insert as "1", etc.
任何后续的查询都可以减去派生的过程，使其插入为“1”等。
Hope it helps.
希望它可以帮助。
Lauren
劳伦
#4
0
I suggest you use two table variables. The @Table1 has an identity seed on the first column. @Table2 has the same first column but no identity seed on it.
我建议您使用两个表变量。@Table1在第一列有一个标识种子。@Table2有相同的第一列，但是没有标识种子。
As you loop through your process,
当你循环你的过程，
Insert into @Table2 from @Table1
then Delete From both Tables as your Process Loops.
然后从两个表中删除进程循环。
On your first pass, the @Table2 will have a a sequential number in the first row starting at 1.
在第一次遍历时，@Table2将在第一行中有一个从1开始的序号。
The second time through the loop your second table might have sequential numbers in the first column starting at say 1081. But if you select the minimum value to a variable
第二次循环时，您的第二个表可能在第一列(从1081开始)中有序号。但是如果你选择一个变量的最小值
(Select @FixSeed = min(RowID) From @Table2)
Then you can update @Table2 to make RowID start at 1 as follows:
然后可以更新@Table2，让RowID从1开始，如下所示:
Update @Table2 Set RowID = RowID - @FixSeed +1
Hope this helps
希望这有助于
#5
0
If you have deleted all the rows in a table, and you want to reset the identity column value.
如果删除了表中的所有行，并希望重置identity列值。
USe DBCC CHECKIDENT command.
使用DBCC CHECKIDENT命令。
DBCC CHECKIDENT(tblPerson, RESEED, 0)
This command will reset PersonId identity column.
此命令将重置PersonId标识列。
Another thing to note is that you may need to enclose the table name in single quotes or square brackets if you are referencing by a full path, or if your table name has spaces in it.
需要注意的另一件事是，如果引用的是完整路径，或者表名中有空格，那么可能需要将表名括在单引号或方括号中。
DBCC CHECKIDENT ( ‘databasename.dbo.orders’,RESEED, 0)
#6
-3
If you need to truncate the table variable in each turn of a while loop, you can put the declare @myTbl (...) statement in the loop. This will recreate the table and reset the identity column on each turn of the loop. However, it has a heavy performance hit. I had fairly tight loop, and redeclaring the table variable relative to delete @myTbl was several times slower.
如果需要在while循环的每个回合中截断表变量，则可以将声明@myTbl(…)语句放入循环中。这将重新创建表，并在每次循环中重置标识列。然而，它的性能却很糟糕。我有相当紧密的循环，并且相对于删除@myTbl重新声明表变量的速度要慢几倍。
- Dan
- 丹