SQL Server - inser the dynami query result a temptable in sql

hi All,

i have a dynamic query like this
----------------------------------------------------------------------------------------------------------------------------
DECLARE @Watts varchar(100)
DECLARE @query VARCHAR(2000)
DECLARE @Id VARCHAR(20)
SET @Id= '151'

SELECT  @Watts  = COALESCE(@Watts+'],[' ,'') + CAST(Watt_Type AS varchar(5))
FROM wattsTable

SET @query =' SELECT * FROM(SELECT ID,Watts,Lamps FROM lampTable
WHERE ID='+@Id+')t  PIVOT  (SUM(Lamps)FOR  Watts IN (['+ @Watts +'])) AS pvt'

EXEC (@Query)
----------------------------------------------------------------------------------------------------------------------------

it was working well and giving a result table like

ID   100     75     50     45     15     15      5     2
-----------------------------------------------------------
1      12    10    0     4     34   98   1   3


but now i want to insert this result table into a temp table
how can i do this

i need advice for this

thanks in advance
MUTHU

Try this example-

Create Table #TEMPDIAG (ID INT, NAME VARCHAR(255))

 

declare @lquery varchar(5000)

set @lquery='Select id,name from sysobjects'

 

INSERT INTO #TEMPDIAG

EXEC (@lQuery)

select *from #TEMPDIAG

Try this and let me know.

hi thank u for ur reply i tried ur query like


INSERT INTO #TempTable
EXEC (@Query)
select *from #TempTable

it throwing error like this

(1 row(s) affected)
Msg 208, Level 16, State 0, Line 18
Invalid object name '#TempTable'.

thanks in advance
MUTHU

Execute these command in following sequence-

Step1: 

Create Table #TEMPDIAG (ID INT, NAME VARCHAR(255))

Step2:

declare @lquery varchar(5000)

set @lquery='Select id,name from sysobjects'

 

INSERT INTO #TEMPDIAG

EXEC (@lQuery)

select *from #TEMPDIAG


Means first create temp table.

actually i generating the columns names dynamically ...
so i cant create the temp table with a static columns name ...
the column name may differ for each time in execution


thanks in advance
MUTHU