将查询结果插入临时表，临时数据不显示。

[tangzaicheng]

各位帮忙看一下这个语句，我将查询结果取出插入临时 表，但查询临时表后却没有任何数据，不知什么原因。以下是代码
CREATE TABLE #bookcount
(
cataloguetype varchar(10),
bookclasscount int,
bookbl float,
moneycount numeric
)


declare @sql nvarchar(100)

set @sql=N'SELECT CatalogueType,COUNT(CatalogueType) as BookClassCount,
CAST(CONVERT(float, COUNT(CatalogueType) * 100) / CONVERT(float,
(SELECT COUNT(*) AS Expr1
FROM ' + @LibName+'.dbo.vBookTypeCatalogue
WHERE (BookType = 0))) AS decimal(18, 2)) as BookBL,SUM(Price) as MoneyCount into #bookcount FROM '+ @LibName+
'.dbo.vBookTypeCatalogue WHERE (BookType = 0) GROUP BY CatalogueType ORDER BY CatalogueType'

exec(@sql)

select * from #bookcount order by cataloguetype

[叶子]

你在外面已经定义了临时表，就是临时表存在了
存在的情况不能直接select into
需要用insert into。不存在的时候用select * into #t 直接创建。

[tangzaicheng]

我刚才将临时表给删除了，使用的select * into #t，但执行时提示这条语句里的表#bookcount无效（select * from #bookcount order by cataloguetype），请问是什么原因

[lvbo1984]

你用了EXEC执行的时候，临时表会被自动释放，你改为全局临时表就可以了。

[qgqch2008]

#bookcount→##bookcount全局临时表

[中国风]

CREATE TABLE #bookcount
(
cataloguetype varchar(10),
bookclasscount int,
bookbl float,
moneycount numeric
)


CREATE TABLE #bookcount(CatalogueType SMALLINT,BookClassCount INT,....)--建表結果

declare @sql nvarchar(100)

--插入#bookcount新數據
set @sql=N'into #bookcount
SELECT 
CatalogueType,COUNT(CatalogueType) as BookClassCount,
CAST(CONVERT(float, COUNT(CatalogueType) * 100) / CONVERT(float,
(SELECT COUNT(*) AS Expr1
FROM ' + @LibName+'.dbo.vBookTypeCatalogue
WHERE (BookType = 0))) AS decimal(18, 2)) as BookBL,SUM(Price) as MoneyCount  FROM '+ @LibName+
'.dbo.vBookTypeCatalogue WHERE (BookType = 0) GROUP BY CatalogueType ORDER BY CatalogueType'

exec(@sql)

select * from #bookcount order by cataloguetype

可這樣用

[studentsky]

exec(@sql)这种方式会阶段查询结果，也就是如果你的@SQL种使用了#tmp表，在EXEC语句之后，select * from #tmp就看不到结果。

如果想看结果，可以set @sql=@sql+ 'select * from #tmp'，然后执行查看结果。

[studentsky]

[Quote=引用 4 楼 qgqch2008 的回复:]
#bookcount→##bookcount全局临时表
[/Quote]

这样性能可能会受到影响