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?
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.
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
