create proc p_merg
as
declare @sql varchar(8000)
set @sql='select * from k001'
select @sql=@sql+' union all select * from '+name --这个地方可能出错
from sysobjects where objectproperty(id,'IsUserTable')=1
and name like 'k0[0-9]' and name<>'k001'
set @sql=substring(@sql,12,@sql)
--查询
exec(@sql)
--将结果生成一个表
exec('select * into 表名 from('+@sql+') a')
go
create proc p_merg
as
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+' union all select * from '+name
from sysobjects where objectproperty(id,'IsUserTable')=1
and name like 'k[0-9][0-9]'
set @sql=substring(@sql,12,@sql)
--查询
exec(@sql)
--将结果生成一个表
exec('select * into 表名 from('+@sql+') a')
go