Create temp table from another temp table in Dynamic Query

Question

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.

The code you posted does not throw that error SQL Fiddle. Maybe the error was coming from another line you haven't shown us. — Martin Smith
– Martin Smith, Commented Jun 3, 2013 at 9:17
First query throw the error and second one execute successfully — Saroop Trivedi
– Saroop Trivedi, Commented Jun 3, 2013 at 9:18
So it is not the dynamic query execution that throws the error, right? The problem happens after EXEC(@Query), specifically when you are attempting to read from the table specified in the dynamic query's INTO clause. — Andriy M
– Andriy M, Commented Jun 4, 2013 at 8:57

Martin Smith · Accepted Answer · 2013-06-03 09:22:31Z

3

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

answered Jun 3, 2013 at 9:22

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Martin Smith Over a year ago

@SaroopTrivedi - Well in that case you need a global ##temp table then as the local one you create in the child scope will be automatically dropped. Or you need to add all code using the table to the same child scope dynamic query. It is actually possible to create a local #temp table in the parent scope then ALTER in a child scope but that is very messy.

Devart · Accepted Answer · 2013-06-03 09:24:12Z

0

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

answered Jun 3, 2013 at 9:24

Devart

120k23 gold badges172 silver badges189 bronze badges

2 Comments

Saroop Trivedi Over a year ago

My ##TempMaster working fine I just want to know why #TempMaster throw error

Devart Over a year ago

Please see @Martin Smith's answer.

Altaf Sami · Accepted Answer · 2013-06-03 09:27:08Z

0

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)

edited Jun 3, 2013 at 9:27

answered Jun 3, 2013 at 9:20

Altaf Sami

8465 gold badges10 silver badges21 bronze badges

Comments

Nenad Zivkovic · Accepted Answer · 2013-06-03 09:32:24Z

0

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.

answered Jun 3, 2013 at 9:32

Nenad Zivkovic

18.6k6 gold badges45 silver badges58 bronze badges

Collectives™ on Stack Overflow

Create temp table from another temp table in Dynamic Query

4 Answers 4

1 Comment

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related