如何把查询出来的结果集插入到临时表？

[sdnwjd]

已经查询出来的结果集是：

select 业务编号=t.yw_id,
项目编号 = t.xm_id,
当日收入金额 = isnull((select dr from t_bb2_in where xm_id = t.xm_id and rq ='2009-11-1' and dept_id='屋'),0.00) ,
累计收入金额 = isnull((select sum(dr) from t_bb2_in where xm_id = t.xm_id and (rq>='2009-11-1' and rq <= '2009-11-2') and dept_id='屋'),0.00),
日期 = '2009-11-2'
from t_bb2_xm t where t.status='启用' and yw_id='收入'
order by t.xm_id

-----------------------------------------------------------------------------------------------------------
我想上面的结果集插入到临时表#test1中，把这个结果集的字段作为#test1的字段，但是提示出错

错误信息为：服务器: 消息 1033，级别 15，状态 1，行 22
除非同时指定了 TOP，否则 ORDER BY 子句在视图、内嵌函数、派生表和子查询中无效。
insert into #test1 select

(
select 业务编号=t.yw_id,
项目编号 = t.xm_id,
当日收入金额 = isnull((select dr from t_bb2_in where xm_id = t.xm_id and rq ='2009-11-1' and dept_id='屋'),0.00) ,
累计收入金额 = isnull((select sum(dr) from t_bb2_in where xm_id = t.xm_id and (rq>='2009-11-1' and rq <= '2009-11-2') and dept_id='屋'),0.00),
日期 = '2009-11-2'
from t_bb2_xm t where t.status='启用' and yw_id='收入'
order by t.xm_id
)

[--小F--]

insert into #test1 select 

( 
select 业务编号=t.yw_id, 
      项目编号 = t.xm_id, 
      当日收入金额 = isnull((select dr from t_bb2_in where xm_id = t.xm_id and rq ='2009-11-1' and dept_id='屋'),0.00) , 
      累计收入金额 = isnull((select sum(dr) from t_bb2_in where xm_id = t.xm_id and (rq>='2009-11-1' and rq <= '2009-11-2') and dept_id='屋'),0.00), 
      日期 = '2009-11-2' 
from t_bb2_xm t where t.status='启用' and yw_id='收入' 

) 
order by t.xm_id 

[--小F--]

insert into #test1 select 

( 
select 
  top 8000 
      业务编号=t.yw_id, 
      项目编号 = t.xm_id, 
      当日收入金额 = isnull((select dr from t_bb2_in where xm_id = t.xm_id and rq ='2009-11-1' and dept_id='屋'),0.00) , 
      累计收入金额 = isnull((select sum(dr) from t_bb2_in where xm_id = t.xm_id and (rq>='2009-11-1' and rq <= '2009-11-2') and dept_id='屋'),0.00), 
      日期 = '2009-11-2' 
from t_bb2_xm t where t.status='启用' and yw_id='收入' 
order by t.xm_id 
) 

[黄_瓜]

提示很明显了，不要order by
存的时候干没有必要排序

[华夏小卒]

select 业务编号=t.yw_id, 
      项目编号 = t.xm_id, 
      当日收入金额 = isnull((select dr from t_bb2_in where xm_id = t.xm_id and rq ='2009-11-1' and dept_id='屋'),0.00) , 
      累计收入金额 = isnull((select sum(dr) from t_bb2_in where xm_id = t.xm_id and (rq>='2009-11-1' and rq <= '2009-11-2') and dept_id='屋'),0.00), 
      日期 = '2009-11-2' 
into #test1
from t_bb2_xm t where t.status='启用' and yw_id='收入' 
order by t.xm_id 

[--小F--]

貌似掉了个别名
insert into #test1 select 

( 
select 业务编号=t.yw_id, 
      项目编号 = t.xm_id, 
      当日收入金额 = isnull((select dr from t_bb2_in where xm_id = t.xm_id and rq ='2009-11-1' and dept_id='屋'),0.00) , 
      累计收入金额 = isnull((select sum(dr) from t_bb2_in where xm_id = t.xm_id and (rq>='2009-11-1' and rq <= '2009-11-2') and dept_id='屋'),0.00), 
      日期 = '2009-11-2' 
from t_bb2_xm t where t.status='启用' and yw_id='收入' 

) t
order by t.xm_id 

[dawugui]

insert into #test1 select 

( 
select 业务编号=t.yw_id, 
      项目编号 = t.xm_id, 
      当日收入金额 = isnull((select dr from t_bb2_in where xm_id = t.xm_id and rq ='2009-11-1' and dept_id='屋'),0.00) , 
      累计收入金额 = isnull((select sum(dr) from t_bb2_in where xm_id = t.xm_id and (rq>='2009-11-1' and rq <= '2009-11-2') and dept_id='屋'),0.00), 
      日期 = '2009-11-2' 
from t_bb2_xm t where t.status='启用' and yw_id='收入' 
) m
order by xm_id 

[qiqi860819]

select top 100 percent

[nianran520]

insert into #test1 
select 业务编号=t.yw_id, 
      项目编号 = t.xm_id, 
      当日收入金额 = isnull((select dr from t_bb2_in where xm_id = t.xm_id and rq ='2009-11-1' and dept_id='屋'),0.00) , 
      累计收入金额 = isnull((select sum(dr) from t_bb2_in where xm_id = t.xm_id and (rq>='2009-11-1' and rq <= '2009-11-2') and dept_id='屋'),0.00), 
      日期 = '2009-11-2' 
from t_bb2_xm t where t.status='启用' and yw_id='收入' 
order by t.xm_id 

[xuejie09242]

select 后加 top 100 percent
就可以了，还是全部的记录。
直接用select into 好了。
select .... into #test1 from .....

[sdnwjd]

insert into #test1 select

(
select 业务编号=t.yw_id,
项目编号 = t.xm_id,
当日收入金额 = isnull((select dr from t_bb2_in where xm_id = t.xm_id and rq ='2009-11-1' and dept_id='屋'),0.00) ,
累计收入金额 = isnull((select sum(dr) from t_bb2_in where xm_id = t.xm_id and (rq>='2009-11-1' and rq <= '2009-11-2') and dept_id='屋'),0.00),
日期 = '2009-11-2'
from t_bb2_xm t where t.status='启用' and yw_id='收入'
) m
order by xm_id

---------------------------------------------------------------------------
select * from #test1

服务器: 消息 208，级别 16，状态 1，行 1
对象名 '#test1' 无效。

怎么提示临时表#test1无效，我想查询出#test1的数据

[sych888]

SELECT ... INTO # FROM ...

[banana_97]

select * into #test1(這里才是臨時表啊)
insert into #test1 這里是表啊

[叶子]

对象名 '#test1' 无效。

说明你上面的插入失败了。

[叶子]

[Quote=引用 10 楼 sdnwjd 的回复:]
insert into #test1 select

(
select 业务编号=t.yw_id,
      项目编号 = t.xm_id,
      当日收入金额 = isnull((select dr from t_bb2_in where xm_id = t.xm_id and rq ='2009-11-1' and dept_id='屋'),0.00) ,
      累计收入金额 = isnull((select sum(dr) from t_bb2_in where xm_id = t.xm_id and (rq>='2009-11-1' and rq <= '2009-11-2') and dept_id='屋'),0.00),
      日期 = '2009-11-2'
from t_bb2_xm t where t.status='启用' and yw_id='收入'
) m
order by xm_id

---------------------------------------------------------------------------
select * from #test1

服务器: 消息 208，级别 16，状态 1，行 1
对象名 '#test1' 无效。

怎么提示临时表#test1无效，我想查询出#test1的数据
[/Quote]

你直接insert into #test1 ，但是还没有创建#test1 ，所以不行。

用select * into #test1 from 是直接创建后插入，两种写法不一样。

[sdnwjd]

明白了

这么写就对了：


select * into #test1 from

(
select 业务编号=t.yw_id,
项目编号 = t.xm_id,
当日收入金额 = isnull((select dr from t_bb2_in where xm_id = t.xm_id and rq ='2009-11-1' and dept_id='屋'),0.00) ,
累计收入金额 = isnull((select sum(dr) from t_bb2_in where xm_id = t.xm_id and (rq>='2009-11-1' and rq <= '2009-11-2') and dept_id='屋'),0.00),
日期 = '2009-11-2'
from t_bb2_xm t where t.status='启用' and yw_id='收入'


)m


select * from #test1 order by 项目编号
drop table #test1

[sdnwjd]

谢谢大家啊


先出去了，一会回来结贴