when i run
i got error msg. what is wrong
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
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.
answered Feb 03, 2012 at 04:51 AM
@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-
answered Feb 03, 2012 at 05:14 AM
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'
answered Feb 03, 2012 at 03:34 PM