把这一句放到QUERY里面执行不就可以了吗?
解决方案 »
- sql server 08的问题 group by 1
- 关于表中各列求和的问题
- 时间查询问题
- sql 2005问题,求助高手
- 还没搞懂如何连接动态语句,谁帮我连接一下啊
- 我这里有个ms sql数据库文件,但是没有后缀。问大家怎么看它是什么数据库文件
- xp pro版下可以安装哪个版的SQL SERVER
- 什么是单用户模式???
- 当对大量的SQL SERVER数据库记录进行转换(如转换为paradox7)时,如何才能最快?用工具也行
- sql 2012 聚合函数 sum的问题
- 在win98下为何不能安装sqlserver2000
- 如何把某个表的表结构变成sql语句,然后由另一个sql server来执行,生成相同的表!急!
exec "select * from "+@table1
如何将结果集,存入一个表变量中。
GO
SELECT * INTO #temptable
FROM OPENQUERY(ServerName,'EXEC(''SELECT * FROM TableName'')')
N_chow(一劍飄香) 怎么要服务器名啊,我就在一台机器上run,
还有类似的方法吗?不要用服务器名的。。谢谢
exec "select * into #temptable from "+@table1
你的方法我想过了因为程序会有多次的计算,用临时表效率太低。帮忙再想想有没好办法·~~
请给个例子·~!!!
@bumen as char(3)
) AS
declare @line as char(3)
declare @tline as char(3)
declare @color as char(6) --对应的颜色
declare @riqi as smalldatetime
declare @gdid as varchar(20)
declare @tname as varchar(20)
declare @liang as int
declare @gdin as int
declare @remain as int
declare @dairu as int
declare @daiyan as int
declare @dairew as int
declare @man as varchar(10)
declare @tdelay as smallint
declare @re as varchar(400)
declare @mk as varchar(100)
declare @mp as varchar(100)
declare @triqi as char(10)
declare @lpd as int
declare @lmp as int
declare @lmk as int
declare @lrd as int
declare @ldqa as int
declare @loqa as int
declare @lelse as intcreate table #repp
(
aline char(3),
alines char(6),
ariqi varchar(10),
ariqis char(6),
agdid varchar(20),
agdids char(6),
atname varchar(20),
atnames char(6),
aliang int,
aliangs char(6),
agdin int,
agdins char(6),
aremain int,
aremains char(6),
adairu int,
adairus char(6),
adaiyan int,
adaiyans char(6),
adairew int,
adairews char(6),
aman varchar(10),
amans char(6),
atdelay smallint,
atdelays char(6),
are char(400),
ares varchar(6),
amk char(100),
amks varchar(6),
amp char(100),
amps varchar(6)
)
if (@bumen<>'all')
begin
----PD不良数
select @lpd=count(*) from gddata where man like 'p%' or man like 'L%'
----MP不良数
select @lmp=count(*) from gddata where man like 'mp%'
----mk不良数
select @lmk=count(*) from gddata where man like 'mk%'
select @lrd=count(*) from gddata where man like 'rd%'
select @ldqa=count(*) from gddata where man like 'dqa%'
select @loqa=count(*) from gddata where man like 'qa%'
select @lelse=count(*) from gddata where man like ''
insert into #repp values(@lpd,@lmp,@lmk,@lrd,@ldqa,@loqa,@lelse,@color,@liang,@color,@gdin,@color,@remain,@color,@dairu,@color,@daiyan,@color,@dairew,@color,@man,@color,@tdelay,@color,@re,@color,@mk,@color,@mp,@color)
declare gdtemp cursor for select line,riqi,gdid,tname,liang,gdin,remain,dairu,daiyan,dairew,man,tdelay,re,mk,mp from gddata where rtrim(man)=@bumen order by riqi desc
open gdtemp
fetch next from gdtemp into @line,@riqi,@gdid,@tname,@liang,@gdin,@remain,@dairu,@daiyan,@dairew,@man,@tdelay,@re,@mk,@mp
while @@fetch_status=0
begin
select @tdelay=datediff(day,@riqi,getdate())
if (@tdelay<=1)
begin
select @color='00ff00' ---显示绿色
end
if (@tdelay=2) or (@tdelay=3)
begin
select @color='ffff00' ---显示黄色
end
if (@tdelay>=4)
begin
select @color='ff99ff' ---显示红色
end
select @triqi=convert(char(4),datepart(year,@riqi))+'-'+convert(char(2),datepart(month,@riqi))+'-'+convert(char(2),datepart(day,@riqi))
insert into #repp values(@line,@color,@triqi,@color,@gdid,@color,@tname,@color,@liang,@color,@gdin,@color,@remain,@color,@dairu,@color,@daiyan,@color,@dairew,@color,@man,@color,@tdelay,@color,@re,@color,@mk,@color,@mp,@color)fetch next from gdtemp into @line,@riqi,@gdid,@tname,@liang,@gdin,@remain,@dairu,@daiyan,@dairew,@man,@tdelay,@re,@mk,@mp
end
close gdtemp
deallocate gdtemp
endif (@bumen='all') -------------按线别列出所有
begin
----PD不良数
select @lpd=count(*) from gddata where man like 'p%' or man like 'L%'
----MP不良数
select @lmp=count(*) from gddata where man like 'mp%'
----mk不良数
select @lmk=count(*) from gddata where man like 'mk%'
select @lrd=count(*) from gddata where man like 'rd%'
select @ldqa=count(*) from gddata where man like 'dqa%'
select @loqa=count(*) from gddata where man like 'qa%'
select @lelse=count(*) from gddata where man like ''
insert into #repp values(@lpd,@lmp,@lmk,@lrd,@ldqa,@loqa,@lelse,@color,@liang,@color,@gdin,@color,@remain,@color,@dairu,@color,@daiyan,@color,@dairew,@color,@man,@color,@tdelay,@color,@re,@color,@mk,@color,@mp,@color)
declare gdline cursor for select line from tline order by tindex
open gdline
fetch next from gdline into @tline
while @@fetch_status=0
begin
declare gdtemp cursor for select line,riqi,gdid,tname,liang,gdin,remain,dairu,daiyan,dairew,man,tdelay,re,mk,mp from gddata where line=@tline order by riqi desc
open gdtemp
fetch next from gdtemp into @line,@riqi,@gdid,@tname,@liang,@gdin,@remain,@dairu,@daiyan,@dairew,@man,@tdelay,@re,@mk,@mp
while @@fetch_status=0
begin
select @tdelay=datediff(day,@riqi,getdate())
if (@tdelay<=1)
begin
select @color='00ff00' ---显示绿色
end
if (@tdelay=2) or (@tdelay=3)
begin
select @color='ffff00' ---显示黄色
end
if (@tdelay>=4)
begin
select @color='ff99ff' ---显示红色
end
select @triqi=convert(char(4),datepart(year,@riqi))+'-'+convert(char(2),datepart(month,@riqi))+'-'+convert(char(2),datepart(day,@riqi))
insert into #repp values(@line,@color,@triqi,@color,@gdid,@color,@tname,@color,@liang,@color,@gdin,@color,@remain,@color,@dairu,@color,@daiyan,@color,@dairew,@color,@man,@color,@tdelay,@color,@re,@color,@mk,@color,@mp,@color)fetch next from gdtemp into @line,@riqi,@gdid,@tname,@liang,@gdin,@remain,@dairu,@daiyan,@dairew,@man,@tdelay,@re,@mk,@mp
end
close gdtemp
deallocate gdtempfetch next from gdline into @tline
end
close gdline
deallocate gdlineend
select * from #repp
drop table #repp
GO
select * from TableName