SQL server数据库 循环建表语句及多表循环插入语句

SQL server数据库的循环建表语句及多表循环插入语句

--建多张表的存储过程

create  proc createtables
as
begin
declare @temp nvarchar(20) 
declare @sql nvarchar(3000)
set @temp = 1 while @temp <=10
begin
set @sql = 'create table zry'+@temp+'
(T_ID numeric(20) not null,
 T_char char(20) collate chinese_prc_ci_as not null,
 T_bigint bigint null,
primary key(T_ID)
);'
exec(@sql)
set @temp = @temp+1
end

end

--调用存储过程
exec createtables

--删除存储过程
drop proc createtables

--多张表插入多条记录的sql语句
  declare @i nvarchar(20)
  declare @j nvarchar(20)
  declare @tbname nvarchar(50)
  declare @sql1 nvarchar(3000)
  set @i =1 while @i <=2
begin 
  set @tbname ='zry'+@i
set @j = 1 while @j <= 2
begin
  set @sql1 = 'INSERT INTO '+@tbname+'(T_ID) values('+@j+')'
exec(@sql1)
set @j=@j+1
end
set @i=@i+1
end