先搞定一张表 然后select * into table1 from 1000万表 select * into table2 from 1000万表 select * into table3 from 1000万表 select * into table4 from 1000万表 select * into table5 from 1000万表 select * into table6 from 1000万表
select * into table400 from 1000万表
select 'insert into'+ name+' values赋值', * from sys.objects where type='u'
先搞定一张表: Insert Into Table1 (field1,...) values(value1,...) Select * Into Table1 from Table1 循环执行25次, 2^25应该超过1kw了 然后按照LS的 Select * Into Table2 from Table1 Select * Into Table3 from Table1 ... Select * Into Table400 From Table1 注意适时提交,否则事务可能会爆掉。
用DAtaFactor软件就可以一下子搞定了。
处理千万条记录,sqlserver比如oracle,哪个更好?
1000万表这张表你可以 insert into tb ([xx]) select [xx] from tb 这样如果开始有一条数据,也运行不了几次就完成了。只是后面时间长一点
declare @tableName varchar(100) declare @sql varchar(8000)declare @col varchar(1000) declare @values varchar(8000)declare @i intdeclare cur cursor for select top 100 name from sysobjects where xtype = 'U' order by Name desc open cur fetch next from cur into @tablename WHILE @@FETCH_STATUS = 0 BEGIN
set @col = '' set @values = '' --获取列名和数字 select @col = @col + ',' + b.name,@values = @values + ',' + c.name from sysobjects a, syscolumns b,systypes c where a.id = b.id and a.xtype = 'U' and a.name = @tableName and b.xtype = c.xtype and b.xUserType = c.xUserType and b.UserType = c.UserType and b.isnullable = 0 and b.colstat <> 1 if (left(@col,1) = ',') Begin set @col = substring(@col,2,len(@col) - 1) end if (left(@values,1) = ',') Begin set @values = substring(@values,2,len(@values) - 1) end
--删除表数据 exec ('truncate table ' + @tableName) print @tablename + ' ####################################################################################' set @i = 1 while @i <20000 begin --varchar set @sql = replace(@values,'varchar','''' + Convert(varchar(20),@i) + '''') --int set @sql = replace(@sql,'int',Convert(varchar(20),@i)) --bigint set @sql = replace(@sql,'int',Convert(varchar(20),@i)) --money set @sql = replace(@sql,'money',Convert(varchar(20),@i)) --datetime set @sql = replace(@sql,'datetime','''' + Convert(varchar(20),getdate()) + '''') --smallint set @sql = replace(@sql,'smallint','''' + Convert(varchar(20),1) + '''') --text set @sql = replace(@sql,'text','''' + Convert(varchar(20),1) + '''')
set @sql = 'Insert Into ' + @tableName + ' (' + @col + ') values (' + @sql + ')' --print @sql set @i = @i + 1
exec(@sql) end fetch next from cur into @tablename END close cur Deallocate cur
然后select * into table1 from 1000万表
select * into table2 from 1000万表
select * into table3 from 1000万表
select * into table4 from 1000万表
select * into table5 from 1000万表
select * into table6 from 1000万表
select * into table400 from 1000万表
Insert Into Table1 (field1,...) values(value1,...)
Select * Into Table1 from Table1 循环执行25次, 2^25应该超过1kw了
然后按照LS的
Select * Into Table2 from Table1
Select * Into Table3 from Table1
...
Select * Into Table400 From Table1
注意适时提交,否则事务可能会爆掉。
1000万表这张表你可以
insert into tb ([xx]) select [xx] from tb
这样如果开始有一条数据,也运行不了几次就完成了。只是后面时间长一点
declare @tableName varchar(100)
declare @sql varchar(8000)declare @col varchar(1000)
declare @values varchar(8000)declare @i intdeclare cur cursor for select top 100 name from sysobjects where xtype = 'U' order by Name desc
open cur
fetch next from cur into @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
set @col = ''
set @values = '' --获取列名和数字
select @col = @col + ',' + b.name,@values = @values + ',' + c.name
from sysobjects a, syscolumns b,systypes c where a.id = b.id and a.xtype = 'U' and a.name = @tableName
and b.xtype = c.xtype and b.xUserType = c.xUserType and b.UserType = c.UserType and b.isnullable = 0 and b.colstat <> 1
if (left(@col,1) = ',')
Begin
set @col = substring(@col,2,len(@col) - 1)
end
if (left(@values,1) = ',')
Begin
set @values = substring(@values,2,len(@values) - 1)
end
--删除表数据
exec ('truncate table ' + @tableName)
print @tablename + ' ####################################################################################' set @i = 1
while @i <20000
begin
--varchar
set @sql = replace(@values,'varchar','''' + Convert(varchar(20),@i) + '''')
--int
set @sql = replace(@sql,'int',Convert(varchar(20),@i))
--bigint
set @sql = replace(@sql,'int',Convert(varchar(20),@i))
--money
set @sql = replace(@sql,'money',Convert(varchar(20),@i))
--datetime
set @sql = replace(@sql,'datetime','''' + Convert(varchar(20),getdate()) + '''')
--smallint
set @sql = replace(@sql,'smallint','''' + Convert(varchar(20),1) + '''')
--text
set @sql = replace(@sql,'text','''' + Convert(varchar(20),1) + '''')
set @sql = 'Insert Into ' + @tableName + ' (' + @col + ') values (' + @sql + ')'
--print @sql
set @i = @i + 1
exec(@sql)
end fetch next from cur into @tablename
END
close cur
Deallocate cur
利用空闲时间,搞了一个试试,这样的效果,我自己使用就够了。。结贴。