CODEWITHSUNDEEP
sql-server
user301016
user301016

Reputation: 2237

How to access temptable after creating it dynamically

I am using the statement 

SELECT    * 
INTO      #gsig_ref  
FROM      gsign 
WHERE     [name] NOT LIKE 'RESERVE%' OR [name] NOT LIKE 'Spare%'



EXECUTE('SELECT * INTO #db1 FROM ' + @db1)
EXECUTE('SELECT * INTO #db2 FROM ' + @db2)

where @db1 will be supplied at runtime (for eg @db1 = '#gsig_ref')

If I say select * from #db1... it says invalid object. Please help me how to access data from #db1.. since i have use this in another query like

SELECT        DISTINCT @p1 INTO #curs_name
FROM          #db1 
WHERE         @p1 NOT IN (SELECT @p2 FROM #db2)
ORDER BY      @p1

How can #db1 be acccessed in the above query and also @p1 is the input variable to this procedure and I should use it for the distinct @p1..

Upvotes: 3

Views: 9437

Answers (5)

arangi Hariprasad
arangi Hariprasad

Reputation: 1

I am able to solve the temp tables by creating them as regular tables and dropping them at the start. I am using select * into temp table from Dynamic parameter. working fine

DECLARE @TABLE VARCHAR(100)='SALES'
IF OBJECT_ID('TMP1') IS NOT NULL DROP TABLE TMP1

SET  @TABLE='SELECT * INTO TMP1 FROM '+@TABLE
PRINT @TABLE
EXEC(@TABLE)

Upvotes: 0

Ram
Ram

Reputation: 16834

Create temp tables in advance before the dynamic sql and then populate then using dynamic sql.

Refer 4th method in http://www.nigelrivett.net/SQLTsql/AccessTempTablesAcrossSPs.html

Hope this helps!

Upvotes: 0

Ed Harper
Ed Harper

Reputation: 21505

You cannot access temp tables declared in dynamic SQL.

However, if you create a temp table before running a dynamic query, you can reference it in that dynamic query.

In your example, this would become

CREATE TABLE #db1
(...table definition...)

CREATE TABLE #db2
(...table definition...)

EXECUTE('INSERT #db1 SELECT * FROM ' + @db1)
EXECUTE('INSERT #db2 SELECT * FROM ' + @db2)

You will also need to construct and execute your second query (using @p1) as dynamic SQL, since you want to use variables as placeholders for column names.

EDIT

There is no simple way to modify this solution if the structure of your source tables varies.

You could write some code to query the information schema to generate and apply dynamic ALTER TABLE scripts to the temp table to make its structure match the source table, but this may be more complexity than you're willing to take on.

Andomar's suggestion of carrying out the whole operation in dynamic SQL may be a better fit.

It might be a good idea to take a step back and re-evaluate whether what you're trying to do is the best solution to whatever problem it is you're trying to solve.

Upvotes: 2

Andomar
Andomar

Reputation: 238096

A local temporary table that is created in a stored procedure is dropped when the procedure ends; other stored procedures, or the calling process, can't see them.

You could generate the entire SQL, effectively moving the temporary table to the dynamic SQL, like:

declare @sql varchar(max);
set @sql = 'select into #t1 ....; select * from #t1';
exec (@sql);

If I read that, I wonder why your outer procedure even needs the temporary table, but there may be good reasons for that.

Another option, if you're sure only one call is executed at the same time, is to use a global temp table. They're declared with a double hash, like ##TempTable. Global temp tables survive the end of their procedure, but can be referenced from multiple sessions.

Upvotes: 3

Svetlozar Angelov
Svetlozar Angelov

Reputation: 21660

You can not reference variables declared in the EXEC(like your temp tables) statement outside that statement.

Upvotes: 0

Related Questions