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

Asked By muthuraman alexander on 29-Nov-11 06:47 AM
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




Jitendra Faye replied to muthuraman alexander on 29-Nov-11 06:50 AM

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.

muthuraman alexander replied to Jitendra Faye on 29-Nov-11 06:53 AM
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
Jitendra Faye replied to muthuraman alexander on 29-Nov-11 06:57 AM
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.

muthuraman alexander replied to Jitendra Faye on 29-Nov-11 07:08 AM
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