海量数据建表常用存储过程

USE [YYG0521]
GO
/****** 对象:  StoredProcedure [dbo].[p_YYG_Raymond_判断要保存的子表名]    脚本日期: 06/14/2007 21:34:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[p_YYG_Raymond_判断要保存的子表名]
 @tablename1 nvarchar(50),
 @table nvarchar(50)=null output
as
 
declare @RecordCount int;          -- 数据总数 
declare @sql nvarchar(2000);    -- SQL语句
declare @TableCount tinyint;       -- 子表数

--查询系统表得到子表数
select @sql = 'select @num2=(select count(*)  from sysobjects where xtype=''u'' and name like ''%'+@tablename1+'%'')'
exec sp_executesql @sql,N'@num2 int out',@TableCount out
print @TableCount

if @TableCount=1
begin
 select @sql='select @num3=(select count(*) from '+@tablename1+')'

 exec sp_executesql @sql,N'@num3 int out',@RecordCount out

 --如果该表记录总数超过30W条，则创建一个新表
 if @RecordCount<300000
 --if @RecordCount<1
  set @table=@tablename1
 else
 begin
  set @TableCount=@TableCount+1
  set @table=@tablename1+'_'+cast(@TableCount as nvarchar)
  
  --创建一个新表，并复制旧表结构
  select @sql = 'select * into '+@table+' from '+@tablename1+' where 0=1'
  exec sp_executesql @sql
 end
end
--如果有N个子表
else
begin
 set @table=@tablename1+'_'+cast(@TableCount as nvarchar)
 select @sql='select @num4=(select count(*) from '+@table+')'
 exec sp_executesql @sql,N'@num4 int out',@RecordCount out
 print @RecordCount
 --如果该表记录总数超过30W条，则创建一个新表
 if @RecordCount>300000
  set @table=@tablename1
 else
 begin
  set @TableCount=@TableCount+1
  set @table=@tablename1+'_'+cast(@TableCount as nvarchar)
  select @sql = 'select * into '+@table+' from '+@tablename1+' where 0=1'
  exec sp_executesql @sql
 end
end