3

I'm using Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) and I'm trying to make a SELECT statement to a table that have been created like this:

DECLARE @Sql AS VARCHAR(1500)

SET @Sql = 'SELECT 1 AS id, ''123'' AS value INTO #tmp_prueba'

EXECUTE ( @Sql )

SELECT * FROM #tmp_prueba

But I'm noticed that the table not exists

How can I get the data from the table?

Sergio Flores
  • 5,231
  • 5
  • 37
  • 60
  • I resolved creating the table before the EXECUTE function and doing an _INSERT_ statement instead a _SELECT_ – Sergio Flores Sep 11 '12 at 20:29
  • 1
    The scope of the table goes out when the EXECUTE statement is executed Please have a look at the following links [Scope of table variable and temp table](http://social.msdn.microsoft.com/Forums/en-US/sqltools/thread/02337dd5-5cfd-40d8-b529-12dc557d6a7e) [A bit about sql server's local temp tables](http://weblogs.sqlteam.com/mladenp/archive/2006/11/03/17197.aspx) – Amal Dev Sep 11 '12 at 19:55

2 Answers2

6

The temporary table that you created in @sql is out-of-scope of the outer query.

Here is one way to do what you want:

DECLARE @Sql AS VARCHAR(1500);

SET @Sql = 'SELECT 1 AS id, ''123'' AS value INTO #tmp_prueba;
            select * from #tmp_prueba'

create table #tmp_prueba (id int, value varchar(255));

insert into #tmp_prueba
    EXECUTE( @Sql );

SELECT * FROM #tmp_prueba

Here are the changes. FIrst, I select everything from the temproary table in the @sql query. Second, I create a temporary table (with the same name in this case) to hold the results. Now, I can insert the results from the execute into the table. Voila! The data is there.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
1

Temp table are created on Tempdb, therefor you can also do this:

SET @Sql = 'SELECT 1 AS id, ''123'' AS value INTO ##tmp_prueba'

EXECUTE ( @Sql )

Select * from tempdb..##tmp_prueba
Hiram
  • 2,679
  • 1
  • 16
  • 15