CODEWITHSUNDEEP
sqlt-sqltemp
Sergio Flores
Sergio Flores

Reputation: 5427

T-Sql getting data from tempdb table

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?

Upvotes: 3

Views: 8229

Answers (2)

Hiram
Hiram

Reputation: 2679

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269773

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.

Upvotes: 6

Related Questions