一个表按记录数分成多个小表,帮忙

验证码识别 2010-06-09 03:42:12
有200万数据的一个表

想分成 40个表,每个 5万数据,表名无要求


越具体越好
...全文
74 8 打赏 收藏 转发到动态 举报
写回复
用AI写文章
8 条回复
切换为时间正序
请发表友善的回复…
发表回复
永生天地 2010-06-09
  • 打赏
  • 举报
回复

alter table tb add tempid int identity(1,1)
go
declare @i int
declare @sql varchar(1000)

set @i=1
while @i<=40
begin
set @sql='select * into table'+ltrim(@i)+' from tb where id between '+ltrim((@i-1)*50000+1)+' and '+ltrim(@i*50000)
--print @sql
exec(@sql)
set @i=@i+1
end

/*
select * into table1 from tb where id between 1 and 50000
select * into table2 from tb where id between 50001 and 100000
select * into table3 from tb where id between 100001 and 150000
select * into table4 from tb where id between 150001 and 200000
select * into table5 from tb where id between 200001 and 250000
select * into table6 from tb where id between 250001 and 300000
select * into table7 from tb where id between 300001 and 350000
select * into table8 from tb where id between 350001 and 400000
select * into table9 from tb where id between 400001 and 450000
select * into table10 from tb where id between 450001 and 500000
select * into table11 from tb where id between 500001 and 550000
select * into table12 from tb where id between 550001 and 600000
select * into table13 from tb where id between 600001 and 650000
select * into table14 from tb where id between 650001 and 700000
select * into table15 from tb where id between 700001 and 750000
select * into table16 from tb where id between 750001 and 800000
select * into table17 from tb where id between 800001 and 850000
select * into table18 from tb where id between 850001 and 900000
select * into table19 from tb where id between 900001 and 950000
select * into table20 from tb where id between 950001 and 1000000
select * into table21 from tb where id between 1000001 and 1050000
select * into table22 from tb where id between 1050001 and 1100000
select * into table23 from tb where id between 1100001 and 1150000
select * into table24 from tb where id between 1150001 and 1200000
select * into table25 from tb where id between 1200001 and 1250000
select * into table26 from tb where id between 1250001 and 1300000
select * into table27 from tb where id between 1300001 and 1350000
select * into table28 from tb where id between 1350001 and 1400000
select * into table29 from tb where id between 1400001 and 1450000
select * into table30 from tb where id between 1450001 and 1500000
select * into table31 from tb where id between 1500001 and 1550000
select * into table32 from tb where id between 1550001 and 1600000
select * into table33 from tb where id between 1600001 and 1650000
select * into table34 from tb where id between 1650001 and 1700000
select * into table35 from tb where id between 1700001 and 1750000
select * into table36 from tb where id between 1750001 and 1800000
select * into table37 from tb where id between 1800001 and 1850000
select * into table38 from tb where id between 1850001 and 1900000
select * into table39 from tb where id between 1900001 and 1950000
select * into table40 from tb where id between 1950001 and 2000000
*/
yibey 2010-06-09
  • 打赏
  • 举报
回复
select top 50000* from tb ,
然后是下一个5万多执行几次算了
lcqtgb 2010-06-09
  • 打赏
  • 举报
回复
[Quote=引用 3 楼 yzm888 的回复:]
引用 1 楼 lcqtgb 的回复:
表结构在哪。。

这个不需要吧
[/Quote]

要科学分类数据。。。

SQL2000很难。。SQL2005很容易。。。
chuifengde 2010-06-09
  • 打赏
  • 举报
回复
alter table [Table] add id int identity(1,1)
go
declare @i int
declare @sql varchar(1000)

set @i=0
while @i<=39
begin
set @sql='select * into [TB'+ltrim(i)+'] from [Table] where id between @i*50000+1 and (@i+1)*50000'
exec(@sql)
set @i=@i+1
end
验证码识别 2010-06-09
  • 打赏
  • 举报
回复
[Quote=引用 2 楼 goodlivelife 的回复:]
弄个行号,然后select * into ?

不过这样的效率可能会很低
[/Quote]

这个行这么指定?
验证码识别 2010-06-09
  • 打赏
  • 举报
回复
[Quote=引用 1 楼 lcqtgb 的回复:]
表结构在哪。。
[/Quote]
这个不需要吧
GOODlivelife 2010-06-09
  • 打赏
  • 举报
回复
弄个行号,然后select * into ?

不过这样的效率可能会很低
lcqtgb 2010-06-09
  • 打赏
  • 举报
回复
表结构在哪。。

34,575

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧