|
业务需求:
test_info表中的一列主键自动递增的xxid,由于中间使用测试数据的时候,插入了很多无关的记录,删除后,XXID不再连续。从新把XXID进行自动递增的数字序列处理。
数据量:60000行。
数据库:SQL server 2005
1、取消XXID的主键和递增标识
2、把数据提取到中间表
select identity(int,1,1) as tid, xxid, htbh into #ttttt from test_info order by HTBH
select * from #ttttt order by xxid
3、更新目标表test_info的XXID
update test_info set xxid = tid from #ttttt where #ttttt.xxid = test_info.xxid
select * from test_info order by xxid
4、删除中间表
drop table #ttttt
附:
复制表数据的命令
--如果表已经存在
insert into 新表 select * from 旧表|站长微信|Archiver|手机版|小黑屋|用友之家 ( 蜀ICP备07505338号|51072502110008 )
GMT+8, 2024-6-1 01:14 , Processed in 0.027359 second(s), 10 queries , Gzip On, Redis On.
Powered by Discuz! X3.5
© 2001-2024 Discuz! Team.