April 23, 2003 at 12:48 am
Here's the scenario (SQL 7):
I would like to use dynamic sql to make a series of Temp tables using a dynamic CREATE TABLE procedure. The CREATE TABLE SQL is built from field information stored in a table. The problem is that when I use EXEC to run my dynamic SQL, the temp table is gone before I can use it. (Do I understand correctly that EXEC creates a separate batch or context, and that's why my temp table is 'gone'?) Here's an example of what I want to do that doesn't work:
USE PUBS
DECLARE @SQL Varchar(500)
SET @SQL = 'Create Table #test (Fld Varchar(4000))'
EXEC (@SQL)
--The next line bombs - temp table is gone.
INSERT INTO #test SELECT au_fname as FLD from Authors
And here's one that does, but it's all contained in the EXEC...
USE PUBS
EXEC ('Create Table tempdb.#test (Fld Varchar(4000))
INSERT INTO #test SELECT au_fname as FLD from Authors
SELECT * FROM #test')
So, is there a way to persist the #temp table, or to run dynamic SQL without EXEC?
WHY do I want to do this? Long story short, I'm trying to automate importing lots of tiny files (groups of 18) whos fixed width definitions change periodically.
Edited by - GregLyon on 04/23/2003 12:49:56 AM
April 23, 2003 at 12:55 am
Make the temp table a global temp table ie ##test. This will persist for the duration of the connection or until you drop the table.
Jeremy
April 23, 2003 at 5:58 am
That will work as long as the process doesn't run concurrently. Because global temp tables are visible to everyone, if ran the proc twice one would fail because the object is already there. Options are to name the name in such a way as to make it unique (appending @@spid is one way), maybe look at using a table variable (SQL2K only). Note that you can use a standard temp table if you do it from an application (connection.execute "Create table...", cn.execute "insert...") because it only uses one connection, you don't have the scoping issue.
Andy
April 23, 2003 at 9:25 am
Unfortunately, I can't use global temp tables because there could be many instances running at the same time, via VB and ASP. (I should have mentioned that in my original post.)
I have a working method where the client app (vb or vba) creates each temp table, but I was trying to do it once in SQL rather than write the code for each client type that may be used in the future.
As for @@spid I'll have to look at that, it's new to me. I have always presumed a table variable would be perfect, but I'm on SQL 7 for now...
Any other ideas???
April 23, 2003 at 9:41 am
@@SPID seems to work just fine. I've appended the @@spid value to the ##temp table name for each user. I plan to drop the tables after using them as well.
I've never used global temp tables before, are there some big reasons NOT to do so now??? I expect the lifetime of each of these small tables to be no more than a minute or so, do I have to worry about them being orphaned if the client process hangs? Does SQL 7 get rid of them eventually? According to BOL it looks like it does...
I'll post the code I used if anyone is interested, basically appended the @@spid to the name of the ##temp table...
Thanks!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply