首页 新闻 搜索 专区 学院

如何把EXEC(@SQL)得到的结果集(列是动态的)插入到一张临时表

1
悬赏园豆:100 [已解决问题] 解决于 2010-11-09 11:25

if object_id('tempdb.dbo.#TotalSalesForMonth') is not null
     drop table #TotalSalesForMonth  --删除临时表 
select * into  #TotalSalesForMonth 
from (
  select
 cast(month(CreateDate) as nvarchar)   as   monthN,
    sum(BaseQuantity)   as   total
  from   (
 select a.CreateDate,b.BaseQuantity
 from  dbo.T_Sales_Order a left join dbo.T_Sales_Product as b
 on a.Id =b.ParentId
 where year(a.CreateDate)=year(getdate())
  ) c
  group by month(CreateDate)
) p
DECLARE @str VARCHAR(500) ,@Sql NVARCHAR(max)
SET @str=''
SELECT @str=@str+','+'['+cast(monthN as nvarchar)+']' FROM #TotalSalesForMonth
SET @str=right(@str,len(@str)-1)
--由于pivot 不支持 在in ()中直接添加字符串,所以需要使用 sql 拼接一下
SET @Sql='select * from  #TotalSalesForMonth pivot (sum(total) for monthN in ('+@str+') ) as pvt '
if object_id('tempdb.dbo.#ForMonth') is not null
     drop table #ForMonth  --删除临时表 
insert into #ForMonth exec(@Sql)

 

(1 行受影响)
消息 208,级别 16,状态 0,第 25 行
对象名  '#ForMonth' 无效。

Jos的主页 Jos | 初学一级 | 园豆:0
提问于:2010-11-08 20:48
< >
分享
最佳答案
0

insert into #ForMonth exec(@Sql)使用这样的SQL语句,首先是表#ForMonth必须存在,如果不存在这个表,要用select * into #ForMonth from 表名

你可以这样写试一下(只后面几句,前面的不变)

SET @Sql='select * into #ForMonth  from  #TotalSalesForMonth pivot (sum(total) for monthN in ('+@str+') ) as pvt '
if object_id('tempdb.dbo.#ForMonth') is not null
     drop table #ForMonth  --删除临时表 
exec(@Sql)

收获园豆:100
天神一 | 小虾三级 |园豆:845 | 2010-11-09 08:49
紧接着你的语句后面 Select * from #ForMonth 依然会报错 消息 208,级别 16,状态 0,第 26 行 对象名 '#ForMonth' 无效。
Jos | 园豆:0 (初学一级) | 2010-11-09 09:49
--
Jos | 园豆:0 (初学一级) | 2010-11-09 11:25
其他回答(1)
0
create table T_Sales_Order
(
id
int,
createdate
datetime
)
go
create table T_Sales_Product
(
parentid
int,
BaseQuantity
int
)
go
if object_id('tempdb.dbo.#TotalSalesForMonth') is not null
drop table #TotalSalesForMonth --删除临时表
select * into #TotalSalesForMonth
from (
select
cast(month(CreateDate) as nvarchar) as monthN,
sum(BaseQuantity) as total
from (
select a.CreateDate,b.BaseQuantity
from dbo.T_Sales_Order a left join dbo.T_Sales_Product as b
on a.Id =b.ParentId
where year(a.CreateDate)=year(getdate())
) c
group by month(CreateDate)
) p
DECLARE @str VARCHAR(500) ,@Sql NVARCHAR(max)
SET @str=''
SELECT @str=@str+','+'['+cast(monthN as nvarchar)+']' FROM #TotalSalesForMonth
SET @str=right(@str,len(@str)-1)
--由于pivot 不支持 在in ()中直接添加字符串,所以需要使用 sql 拼接一下

SET @Sql='select * into #ForMonth from #TotalSalesForMonth pivot (sum(total) for monthN in ('+@str+') ) as pvt '
if object_id('tempdb.dbo.#ForMonth') is not null
drop table #ForMonth --删除临时表
exec(@Sql)

表中数据分别为T_Sales_Order:

1 2010-11-8 0:00:00
2 2010-11-8 0:00:00
3 2010-11-8 0:00:00

T_Sales_Product:

1 22222
3 44444
寒狐 | 园豆:433 (菜鸟二级) | 2010-11-09 11:34
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册