检索数据的SQL语句如下:declare @sql varchar(8000) set @sql='' select @sql=@sql+' union all select * from '+name from sysobjects where type='U' and name like 'sz%' set @sql=stuff(@sql,1,10,'') exec(@sql)
select identity(int,1,1) as id,* into tablename from ( select * from sz1 union all select * from sz2 union all select * from sz3 union all select * from sz4 )t
select identity(int,1,1) as id,* into tb from ( select * from sz1 union all select * from sz2 union all select * from sz3 union all select * from sz4 .......... ) t
declare @name varchar(50) declare cur_sz cursor for select name from sysobjects where type='U' and name like 'sz%' open cur_sz fetch cur_sz into @name begin select identity(int 1,1) as ID, * into tablename from @name end fetch cur_sz into @name close cur_sz deallocate cur_sz
修改: ———————————— declare @name varchar(50) declare cur_sz cursor for select name from sysobjects where type='U' and name like 'sz%' open cur_sz fetch cur_sz into @namewhile @@fetch_status=0begin select identity(int 1,1) as ID, * into tablename from @name end fetch cur_sz into @name close cur_sz deallocate cur_sz
修改create table tablename(id int identity(1,1),字段列表...)declare @name varchar(50) declare cur_sz cursor for select name from sysobjects where type='U' and name like 'sz%' open cur_sz fetch next from cur_sz into @namewhile @@fetch_status=0 exec('insert into tablename(字段列表) select * from '+@name) fetch next from cur_sz into @name close cur_sz deallocate cur_sz
set @sql=''
select @sql=@sql+' union all select * from '+name from sysobjects where type='U' and name like 'sz%'
set @sql=stuff(@sql,1,10,'')
exec(@sql)
into tablename
from (
select * from sz1
union all
select * from sz2
union all
select * from sz3
union all
select * from sz4
)t
into tb
from
(
select * from sz1
union all
select * from sz2
union all
select * from sz3
union all
select * from sz4
..........
)
t
declare cur_sz cursor for select name from sysobjects where type='U' and name like 'sz%'
open cur_sz
fetch cur_sz into @name
begin
select identity(int 1,1) as ID, * into tablename from @name
end
fetch cur_sz into @name
close cur_sz
deallocate cur_sz
————————————
declare @name varchar(50)
declare cur_sz cursor for select name from sysobjects where type='U' and name like 'sz%'
open cur_sz
fetch cur_sz into @namewhile @@fetch_status=0begin
select identity(int 1,1) as ID, * into tablename from @name
end
fetch cur_sz into @name
close cur_sz
deallocate cur_sz
declare cur_sz cursor for select name from sysobjects where type='U' and name like 'sz%'
open cur_sz
fetch next from cur_sz into @namewhile @@fetch_status=0
exec('insert into tablename(字段列表) select * from '+@name)
fetch next from cur_sz into @name
close cur_sz
deallocate cur_sz