DECLARE xcursof CURSOR KEYSET FOR select 第一列 from aDECLARE @name varchar(40)OPEN xcursor create table #temp(型号 varchar(100),功能 varchar(200)) FETCH NEXT FROM xcursor INTO @name WHILE (@@fetch_status <> -1) BEGIN insert into #temp select * from @name FETCH NEXT FROM xcursor INTO @name ENDCLOSE xcursor DEALLOCATE xcursor GO这样你要得所有结果就都在表#temp中了 select * from #temp drop table #temp
DECLARE xcursof CURSOR KEYSET FOR select 第一列 from aDECLARE @name varchar(40)OPEN xcursor create table #temp(型号 varchar(100),功能 varchar(200)) FETCH NEXT FROM xcursor INTO @name WHILE (@@fetch_status <> -1) BEGIN exec('insert into #temp select * from '+ @name) FETCH NEXT FROM xcursor INTO @name ENDCLOSE xcursor DEALLOCATE xcursor GO这样你要得所有结果就都在表#temp中了 select * from #temp drop table #temp
是可以写的。 但我觉得这样设计的东西是不科学的把, 操作起来把方便啊。 create proc sample_proc as declare @product_num varchar(10) declare sample_cur cursor for select 产品编号 from 表a open sample_cur fetch next from sample_cur into @product_num while (@fetch_status<>-1) do begin select 型号,功能 from @product_num--------------->具体要实现什么,你的要求不明确可以--------在此修改 fetch next from sample_cur into @product_num end close sample_cur deallocate sample_cur
create procedure test as declare @tbname varchar(100) declare #aa cursor for select 产品编号 from 表a open #aa fetch next from #aa into @tbname while @@fetch_status=0 begin exec('select * from ['+@tbname+']' fetch next from #aa into @tbname end close #aa deallocate #aago --测试 exec test
如果你要得到多个表的并集,比如要1998年所有的:declare @表名 varchar(100) set @表名='' select @表名=@表名+'select * from '+产品编号+' union all ' from 表a where 时间 like '1998%' set @表名=left(@表名,len(@表名)-10) exec(@表名)
KEYSET
FOR select 第一列 from aDECLARE @name varchar(40)OPEN xcursor
create table #temp(型号 varchar(100),功能 varchar(200))
FETCH NEXT FROM xcursor INTO @name
WHILE (@@fetch_status <> -1)
BEGIN
insert into #temp select * from @name
FETCH NEXT FROM xcursor INTO @name
ENDCLOSE xcursor
DEALLOCATE xcursor
GO这样你要得所有结果就都在表#temp中了
select * from #temp
drop table #temp
KEYSET
FOR select 第一列 from aDECLARE @name varchar(40)OPEN xcursor
create table #temp(型号 varchar(100),功能 varchar(200))
FETCH NEXT FROM xcursor INTO @name
WHILE (@@fetch_status <> -1)
BEGIN
exec('insert into #temp select * from '+ @name)
FETCH NEXT FROM xcursor INTO @name
ENDCLOSE xcursor
DEALLOCATE xcursor
GO这样你要得所有结果就都在表#temp中了
select * from #temp
drop table #temp
但我觉得这样设计的东西是不科学的把,
操作起来把方便啊。
create proc sample_proc
as
declare @product_num varchar(10)
declare sample_cur cursor for
select 产品编号 from 表a
open sample_cur
fetch next from sample_cur into @product_num
while (@fetch_status<>-1) do
begin
select 型号,功能 from @product_num--------------->具体要实现什么,你的要求不明确可以--------在此修改
fetch next from sample_cur into @product_num
end
close sample_cur
deallocate sample_cur
as
declare @tbname varchar(100)
declare #aa cursor for select 产品编号 from 表a
open #aa
fetch next from #aa into @tbname
while @@fetch_status=0
begin
exec('select * from ['+@tbname+']'
fetch next from #aa into @tbname
end
close #aa
deallocate #aago
--测试
exec test
set @表名=''
select @表名=@表名+'select * from '+产品编号+' union all ' from 表a where 时间 like '1998%'
set @表名=left(@表名,len(@表名)-10)
exec(@表名)