March 23, 2010 at 12:48 pm
I am using dynamic SQL to build a query that looks like the following:
SELECT BEGDOC
INTO [#EDC-GH-GF]
FROM TABLE 1
WHERE PROCESSID = 'EDC-GH-GF'
When I use a PRINT(@QUERY) command to see the results, and then run that query, it runs perfectly. IF I use EXEC(@QUERY) to run it directly, the temp table never gets made.
If I remove the # and make it a permanent table, it runs in both regular and dynamic sql.
March 23, 2010 at 12:53 pm
huston.dunlap (3/23/2010)
I am using dynamic SQL to build a query that looks like the following:SELECT BEGDOC
INTO [#EDC-GH-GF]
FROM TABLE 1
WHERE PROCESSID = 'EDC-GH-GF'
When I use a PRINT(@QUERY) command to see the results, and then run that query, it runs perfectly. IF I use EXEC(@QUERY) to run it directly, the temp table never gets made.
If I remove the # and make it a permanent table, it runs in both regular and dynamic sql.
Basic misconception about the lifespan of a temp table. When you run EXEC(@QUERY), the temp table DID get created. And then, your scope ended and you returned from the EXEC. When that happened, the temp table got dropped. If you need the temp table to persist, make it before running the EXEC(@QUERY), and change the query to be an INSERT INTO [#EDC-GH-GF] SELECT BEDDOC FROM TABLE1 ...
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
March 24, 2010 at 7:46 am
Very good to know, thank you!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply