我用exec('select * into #t from table')怎么不能建立临时表

[mqflf]

CREATE PROCEDURE [Meter_XiaoQu] as
exec('select * into #t from table')
select * from #t
go

执行存储过程
提示#t表无效

[lsxaa]

CREATE PROCEDURE [Meter_XiaoQu] as
exec('select * into ##t from table')
select * from ##t
go

[sdhdy]

exec('select * into #t from table')
你生成的这个临时表，只在这条语句执行的时候存在，执行完了，该临时表也就不存在了
可以用全局临时表

[shuiniu]

最好不要用全局临时表!

[xuam]

CREATE PROCEDURE [Meter_XiaoQu] as
exec('insert into #t select * from table')
select * from #t
go

[shuiniu]

CREATE PROCEDURE [Meter_XiaoQu] as
create table #t....
exec('insert into #t select * from table')
select * from #t
go

[Frewin]

CREATE PROCEDURE [Meter_XiaoQu] as
insert into #t select * from table
select * from #t
go

[Frewin]

CREATE PROCEDURE [Meter_XiaoQu] as
select * into #t from table
select * from #t
go

[hglhyy]

楼上的　'insert into 　只能插已存在的表

[hglhyy]

不知道楼主要达到个什么目的，其它建立一个过渡表，之后再删除也一样
如：
CREATE PROCEDURE [Meter_XiaoQu] as
create table #t....
exec('insert into #t select * from table')
select * from #t
go

[lsxaa]

直接这样就行了

CREATE PROCEDURE [Meter_XiaoQu] as
select * into #t from table --把exec去掉了
select * from #t
go

[playyuer]

exec('select * into #t from table')
相当于另一次连接

[kenvinp]

execute('select * into #t from table')

[yjf486]

CREATE PROCEDURE [Meter_XiaoQu] as
insert into #t select * from table
select * from #t
go

[mqflf]

存储过程执行完成后，怎么手动来释放##t临时表呢？

[lsxaa]

drop table ##t

[mqflf]

CREATE PROCEDURE [Meter_XiaoQu]
@XiaoQuName nvarchar(100),
@dateend datetime,
@page int,
@pagecount int
AS
set nocount on
--select cast(@XiaoQuName as nvarchar)
--declare @XiaoQuName nvarchar(100)
--set @XiaoQuName='110宿舍'

select id,入网时间 into #XinXi from XhuJu_user_XinXi where 用户分类 = '小区宽带' and 单位名称 = @XiaoQuName
declare @datebegin datetime--,@dateend datetime
select @datebegin=min(入网时间) from #XinXi
--set @dateend=getdate()
declare @rc int
set @rc=datediff(month,@datebegin,@dateend)+1

set rowcount @rc
select sn=identity(int,0,1) into #t from syscolumns
set rowcount 0
select year=year(dateadd(month,sn,@datebegin)),month=month(dateadd(month,sn,@datebegin))
into #t1 from #t

select * into #XinXi_user from #XinXi,#t1
select XinXi_id,Y_id=year(日期),M_id=month(日期),金额 into #ChuZhang from XhuJu_user_ChuZhang a left join XhuJu_user_XinXi b on a.XinXi_id = b.id where b.单位名称 = @XiaoQuName
select XinXi_id,Y_id,M_id,金额=sum(金额) into #ChuZhang_user from #ChuZhang group by XinXi_id,Y_id,M_id order by XinXi_id

select XinXi_id,Y_id=year(日期),M_id=month(日期),金额 into #RuZhang from XhuJu_user_RuZhang a left join XhuJu_user_XinXi b on a.XinXi_id = b.id where b.单位名称 = @XiaoQuName
select XinXi_id,Y_id,M_id,金额=sum(金额) into #RuZhang_user from #RuZhang group by XinXi_id,Y_id,M_id order by XinXi_id

select XinXi_id,Y_id=year(日期),M_id=month(日期),金额 into #JianMian from XhuJu_user_JianMian a left join XhuJu_user_XinXi b on a.XinXi_id = b.id where b.单位名称 = @XiaoQuName
select XinXi_id,Y_id,M_id,金额=sum(金额) into #JianMian_user from #JianMian group by XinXi_id,Y_id,M_id order by XinXi_id

select a.id,a.year,a.month,出帐=isnull(b.金额,0) into #XinXi_1 from #XinXi_user a left join #ChuZhang_user b on (a.id = b.XinXi_id and a.year = b.Y_id and a.month = b.M_id)
select a.id,a.year,a.month,a.出帐,入帐=isnull(b.金额,0) into #XinXi_2 from #XinXi_1 a left join #RuZhang_user b on (a.id = b.XinXi_id and a.year = b.Y_id and a.month = b.M_id)
select a.id,a.year,a.month,a.出帐,a.入帐,减免=isnull(b.金额,0) into #XinXi_3 from #XinXi_2 a left join #JianMian_user b on (a.id = b.XinXi_id and a.year = b.Y_id and a.month = b.M_id)

declare @s2 varchar(8000)
select @s2=''
select @s2=@s2+',['+cast(year as varchar)+'_'+cast(month as varchar)+']=max(case when year='+cast(year as varchar)+' and month='+cast(month as varchar)+' then cast(出帐 as varchar)+'',''+cast(入帐 as varchar)+'',''+cast(减免 as varchar) else '''' end)' from #XinXi_3 group by year,month

--select b.联系人,b.入网时间,b.联系电话,b.联系手机,a.* from (select id'+@s2+' into #Mqflf from #XinXi_3 group by id) a left join XhuJu_user_XinXi b on a.id = b.id

declare @pageC as int
set @pageC=@pagecount*@page

exec('select id'+@s2+' into ##t0 from #XinXi_3 group by id')
exec('select b.联系人,b.入网时间,b.联系电话,b.联系手机,a.* from (select top '+@pagecount+' * from ##t0 where id not in(select top '+@pageC+' id from ##t0 order by id) order by id) a left join XhuJu_user_XinXi b on a.id = b.id')

GO

这是我的存储过程，因为第一次按触SQL，所以很乱，就这些还大部分是邹建大哥的代码呢？

这个存储过程我要达到的目的就是从
create table XhuJu_user_XinXi
(
id bigint
单位名称 nvarchar(100),
单位地址 nvarchar(100),
联系人 nvarchar(20)
联系电话 char(20),
联系手机 char(20),
详细地址 nvarchar(200),
入网时间 datetime,
)
create table XhuJu_user_ChuZhang
(
id bigint not null, /*1234567890*/
XinXi_id bigint,
日期 datetime, /*执行出帐操作的时间*/
金额 int, /*单位￥元*/
)

create table XhuJu_user_RuZhang
(
id bigint not null, /*1234567890*/
XinXi_id bigint,
日期 datetime, /*执行出帐操作的时间*/
金额 int, /*单位￥元*/

)

create table XhuJu_user_JianMian
(
id bigint not null, /*1234567890*/
XinXi_id bigint,
日期 datetime, /*执行出帐操作的时间*/
金额 int, /*单位￥元*/
)

以上四个表中取出数据组合成这样的一张表
id 联系人 入网时间 联系电话 联系手机 2002_3 2002_4 ......


说明：每条记录显示每个每一用户的统计信息，
id 是用户在XhuJu_user_XinXi表中的id值(后面三张表的XinXi_id字段=XhuJu_user_XinXi表的id字段
联系人...
入网时间...
联系电话...
联系手机...
后面跟的字段通过查询参数不同动态生成。比如某用户在2002年4月入网，那么就从2002年4月开始每月生成一字段，至@dateend参数的日期，字段值就=此用户在XhuJu_user_ChuZhang、XhuJu_user_RuZhang、XhuJu_user_JianMian三张表中当月金额的和，由,号分隔开。

存储过程的四个参数
@XiaoQuName 单位名称，通过此参数，只显示某一单位的用户
@dateend 截止日期，
@page 查看的页号
@pagecount 一页显示几条记录