execute SQL problem

The problem is the scope of the query. Dynamic queries have their own scope and after its execution the #temptable would be dropped. To see this happening use this script

DECLARE @sql VARCHAR(200)
SELECT  @sql = 'select * into #temptbl from Employee 
select * from #temptbl
select * from [tempdb].sys.[tables] where name like ''#temptbl%'''

EXECUTE (@sql)

SELECT  *
FROM    [tempdb].sys.[tables]
WHERE   [name] LIKE '#temptbl%'

You would be able to fetch the records, and you would see the existence of the #temptbl table. But after the execution the table would not exist anymore.

So the solutions could be to use the SELECT statement inside the dynamic query OR use a GLOBAL temporary table.

But first thing is why you need DYNAMIC query for this? Please let us know then we might be able to lead you to a better approach.

@Usman, Is right Dynamic queries have their own scope and after its execution the #temp table would be dropped. So you need to check whether you can achieve your requirement without dynamic query or not other wise you will have to put your select statement inside the Dynamic query as @Usamn explains in his example.

Another way you can use as given in below example-

CREATE TABLE EMP(ID INT,NAME VARCHAR(100))

INSERT INTO EMP VALUES(1,'SQL')

CREATE TABLE #EMP(ID INT,NAME VARCHAR(100))

DECLARE @sql VARCHAR(200)

SELECT @sql = 'INSERT INTO #EMP(ID,NAME) select * from EMP'

EXECUTE (@sql)

SELECT * FROM #EMP

DROP TABLE #EMP

DROP TABLE EMP

I have query like this and i want the record of this query in seperate temp table. can you please help me now? DECLARE @listCol VARCHAR(2000) DECLARE @query VARCHAR(4000) declare @sold_to_id varchar(20)='10259300' SELECT @listCol = STUFF(( SELECT DISTINCT '],[' + field_name FROM VW_SMART_TRACKER_DATA where sold_to_id=@sold_to_id ORDER BY '],[' + field_name FOR XML PATH('') ), 1, 2, '') + ']' SET @query = ' SELECT * INTO #TEMPTBL FROM (SELECT sold_to_id, stuid, web_grp_id, field_name, value FROM VW_SMART_TRACKER_DATA WHERE sold_to_id = ' + @sold_to_id + ' and field_name is not null ) src PIVOT (max(value) FOR Field_name IN (' +@listCol+')) AS pvt select * from #temptbl' EXECUTE (@query)