自动建表 1

自动建立表结构
Create Procedure AutoCreateTable
@tablename varchar(40)
as
begin
declare @s1 varchar(8000),@s2 varchar(8000),@s3 varchar(8000)
select @s1='Create table '+@tablename+'  ( tagtime datetime ',@s2='',@s3=''
select @s1=@s1+',['+rtrim(tagname)+'] varchar(10) ' from bj01data where ID between 221 and 310 order by ID
select @s2=@s2+',['+rtrim(tagname)+'] varchar(10) ' from bj01data where ID between 311 and 410 order by ID
select @s3=@s3+',['+rtrim(tagname)+'] varchar(10) ' from bj01data where ID between 402 and 492 order by ID
select @s1=stuff(@s1,20,1,'')
select @s3=@s3+')'
exec (@s1+@s2+@s3 )
end

用以下语句来调用存储过程

declare @tablename varchar(100)
declare @i int
set @tablename=''
set @i=1
while @i<=365
begin
--exec('exec AutoCreateTable'+ ' GL'+''+@i'')
select @tablename='GY.dbo..GY'+right(@i+1000 ,3)
exec ('exec AutoCreateTable '+ ''''+@tablename+'''')
select @i=@i+1
end