如何自动平分生成50个表,请专家答疑解惑

Question

--  执行脚本如果是一个表就会有184,756行结果,问题:如何自动平分生成50个表,请专家答疑解惑
;WITH t AS 
(
SELECT TOP 20 rn=ROW_NUMBER()OVER(ORDER BY @@spid)
FROM sysobjects 
)  
SELECT [ID]=Identity(INT,1,1),concat_ws(' ',a.rn,b.rn,c.rn,d.rn,e.rn,f.rn,g.rn,h.rn ,i.rn,j.rn) as notext
     

INTO [#tb66] 
FROM T a
JOIN t b ON a.rn<b.rn
    JOIN t c ON b.rn<c.rn
        JOIN t d ON c.rn<d.rn
            JOIN t e ON d.rn<e.rn
                JOIN t f ON e.rn<f.rn
            JOIN t g ON f.rn<g.rn
        JOIN t h ON g.rn<h.rn
    JOIN t i ON h.rn<i.rn
JOIN t j ON i.rn<j.rn 

Answer 1

declare @i int,@k int
declare @sql nvarchar(max)
select @i=count()/50+1 from #tb66
set @k=0
while @k<50
begin
select min(id),max(id) from #tb66
where id between @i@k+1 and @i*(@k+1)
set @k=@k+1
end
每个表的最小ID和最大ID给你算出来了，至于怎么弄到50个表里面，在循环内用动态语句去处理就好
不用循环也可以。
declare @i int
declare @sql nvarchar(max)
select @i=count()/50+1 from #tb66
select 'insert into table'+left(number+1,2)+' from #tb66
where id between '+ left( number@i+1,10)+' and '+ left((number+1)*@i,10)
from master..spt_values where type='P' and number between 0 and 49
同样能求出来最小ID和最大ID，分组为NUMBER+1,生成插入脚本