CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Saroop Trivedi
Saroop Trivedi

Reputation: 2275

Create temp table from another temp table in Dynamic Query

I try to create #temp table from another #temp table then it's through the error. 

 Set @Query = 'Select Work_Order_No,ServiceCode,StageNo,ItemWeight,StagePercentage,FebLocation 
    INTO #TempMaster
    FROM #Temp '
EXEC(@Query)

Above query throw the error 

Invalid object name '#TempMaster'.

and if I execute this query 

Set   @Query = 'Select Work_Order_No,ServiceCode,StageNo,ItemWeight,StagePercentage,FebLocation 
    INTO ##TempMaster
    FROM #Temp '

EXEC(@Query)

Then it's working fine. What is different between these two statement. What is reason that first query throw error and second query run successfully.

Upvotes: 2

Views: 2889

Answers (4)

Nenad Zivkovic
Nenad Zivkovic

Reputation: 18559

If you create #temp table inside dynamic query it is only valid inside that dynamic query and you can not use it later after dynamic query has finished executing.

If you really need to fill your table dynamically - you should create it up front.

Upvotes: 0

Altaf Sami
Altaf Sami

Reputation: 846

Do like this:

set @Query = 'select Work_Order_No,ServiceCode,StageNo,ItemWeight,StagePercentage,FebLocation 
INTO #TempMaster1
FROM tablename

select * into #t from #TempMaster1

drop table #TempMaster1
drop table #t '

exec (@Query)

Upvotes: 0

Devart
Devart

Reputation: 122042

Try this one -

DECLARE @SQL NVARCHAR(MAX)

SELECT @SQL = '
IF OBJECT_ID (N''tempdb.dbo.##TempMaster'') IS NOT NULL
   DROP TABLE ##TempMaster

SELECT Work_Order_No,ServiceCode,StageNo,ItemWeight,StagePercentage,FebLocation 
INTO ##TempMaster
FROM #Temp'

EXEC sys.sp_executesql @SQL

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 453910

Following the edit the difference is that local #temp tables created in a child scope are automatically dropped when the scope exits and are not visible to the parent scope. ## prefixed tables are global temporary tables and not dropped automatically when the scope exits. Instead they are dropped when the creating connection is closed and no other connection is currently accessing it.

There is no apparent requirement to use EXEC here anyway so you could avoid this issue by using the code you originally posted.

SELECT Work_Order_No,
       ServiceCode,
       StageNo,
       ItemWeight,
       StagePercentage,
       FebLocation
INTO   #TempMaster
FROM   #Temp

Upvotes: 3

Related Questions