Sql脚本动态创建表

今天写了个脚本文件实现动态创建多张表，表明以日期形式出现，并实现参数传递，觉得很有意义，供大家参考

DECLARE @i int
DECLARE @createtable varchar(100)
Set @i = 0
WHILE @i < 2
IF not EXISTS (SELECT name FROM sysobjects

WHERE name =@createtable AND type = 'U')

begin
Set @i =@i +1
set @createtable=CONVERT(nvarchar(10),getdate()+@i,112)//以20130912形式出现
set @createtable='create table '+'"'+ @createtable+'"'+'(天 int,店 int,游戏编号 int,销售额 float)'

exec(@createtable)

end

 

往数据表中添加数据

DECLARE @i int
DECLARE @createdata varchar(100)
Set @i = 0
WHILE @i < 2
begin
Set @i =@i +1
set @createdata=CONVERT(nvarchar(10),getdate()+@i,112)
set @createdata='insert into '+'"'+ @createdata+'"'+'(天,店,游戏编号,销售额) values(2,2,2,2)'
exec(@createdata)

end

 

每张表中插入多条数据

DECLARE @i int
DECLARE @k int
DECLARE @createdata varchar(100)
Set @i = 0
WHILE @i < 2
begin
Set @i =@i +1
set @k=0
WHILE @k<10
begin
set @k=@k+1
set @createdata=CONVERT(nvarchar(10),getdate()+@i,112)
set @createdata='insert into '+'"'+ @createdata+'"'+'(天,店,游戏编号,销售额) values(2,2,2,2)'
exec(@createdata)
end

end