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

Question

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)) pDECLARE @str VARCHAR(500) ,@Sql NVARCHAR(max)SET @str=''SELECT @str=@str+','+'['+cast(monthN as nvarchar)+']' FROM #TotalSalesForMonthSET @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' 无效。

Answer 1

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)