This topic has been archived. It cannot be replied.
-
工作学习 / IT技术讨论 / In SQL Server, how to rearrange identity collumn? For example: old value is 1,3,5,9, how to change to 1,2,3,4 and subsequently to 5.
-cleveland(alex);
2002-10-13
(#795850@0)
-
So your identity column is auto number?
-jeffrey815(Smartiecat);
2002-10-13
(#795875@0)
-
yes
-cleveland(alex);
2002-10-13
(#796006@0)
-
以前我们的做法是把indentity去了再加上，不过好象只限制与单表，如果是有关联的字段，估计还要麻烦点
-hugefox(长得象熊的狐狸);
2002-10-13
(#795970@0)
-
You get the point. Can I implement it through T-SQL script? and how to renumber it? Thank you very much
-cleveland(alex);
2002-10-13
(#796009@0)
-
Theoretically you can't change the value of the identity column from a table. But there is a solution working around it.Assume A is the table you are working with.
1.Create a new table B with the same structure as table A but without the identity definition for that column
2. copy the data from table A to table B
(insert into B select * from A)
3. update the column in b
4. truncate table A
5. set identity_insert A on
insert A select * from B
set identity_insert A off
-yangn(Raymond);
2002-10-13
{371}
(#796020@0)
-
Thank you. I'll try it.
-cleveland(alex);
2002-10-13
(#796152@0)
-
一般最简单的做法是把那个字段删了再重加上。不过这个做法只限于没有表是她的子表而且字段本身无意义。如果情况复杂，具体研究
-hugefox(长得象熊的狐狸);
2002-10-13
(#796047@0)
-
不可以.delete record 并不影响当前的identity value. For example, there are 4 records in one table and the identity values are 1,2,3,4. If you delete the record with identity value 1 and readd it, the identity will be 5.
-yangn(Raymond);
2002-10-13
(#796117@0)
-
没让你delete record啊，把那个identity的字段删了再加上就从头开始排了
-hugefox(长得象熊的狐狸);
2002-10-13
(#796130@0)
-
1.this method won't work if the identity column is referred by other table
2. If the number of records is very high, it takes too much time to drop that column and to create again.
-yangn(Raymond);
2002-10-13
(#796139@0)
-
Raymond DX, are there any other way to change a table's identity value except truncate table?
-cleveland(alex);
2002-10-13
(#796155@0)
-
Microsoft provides DBCC CHECKIDENT to reset the identity value. But it is still impossible to update the pre-existing identity value with that utility. There is no easy way to implement that.
-yangn(Raymond);
2002-10-13
(#796175@0)