Tsql Select into Temp Table from Dynamic SQL

TSQL select into Temp table from dynamic sql

A working example.

DECLARE @TableName AS VARCHAR(100)
SELECT @TableName = 'YourTableName'

EXECUTE ('SELECT * INTO #TEMP  FROM ' + @TableName +'; SELECT * FROM #TEMP;')

Second solution with accessible temp table

DECLARE @TableName AS VARCHAR(100)
SELECT @TableName = 'YOUR_TABLE_NAME'

EXECUTE ('CREATE VIEW vTemp AS
        SELECT *
        FROM ' + @TableName)
SELECT * INTO #TEMP  FROM vTemp 

--DROP THE VIEW HERE      
DROP VIEW vTemp

/*START USING TEMP TABLE
************************/
--EX:
SELECT * FROM #TEMP

--DROP YOUR TEMP TABLE HERE
DROP TABLE #TEMP

Select * into Temp Table from Dynamic SQL Results

It is risky because you run the risk of collisions, and not very efficient, but you can drop the dynamic results into a ##TempPivot

Set @query = '

IF OBJECT_ID(''tempdb..##TempPivot'') IS NOT NULL
  DROP TABLE ##TempPivot

SELECT *,' + @cols + ' 
 Into ##TempPivot
 From (
        select *
        from #TempCosts
        where year = ' + @RealYear + '
       ) x
    pivot 
    (
        sum(Amount)
        for AccountRef_FullName in (' + @cols + ')
    ) p 
'
Exec(@query)

Select * Into #Temp from ##TempPivot

Drop Table ##TempPivot

How can I insert dynamic sql into temp table?

I run this code and it returned me the test rows I'd created.

declare @query nvarchar(100)
set @query = N'select * into ##TMPTblTest from tblTest'

exec sp_executesql @query;

select * from ##TMPTblTest

You are using a global temporary table. If you make a select on it, I think it will work.

Select * into #Temp not working in dynamic SQL

The cause of this error is that a temp table is session bound and your dynamic SQL runs in a separate session.

Use a global temp table (prefix: ##) and make this one unique by naming (i.e. add a guid to the name). This way the dynamic SQL can see it.

Different types temporary tables: http://www.sqlines.com/articles/sql-server/local_and_global_temporary_tables

Dynamic SQL results into temp table in SQL Stored procedure

Try:

SELECT into #T1 execute ('execute ' + @SQLString )

And this smells real bad like an sql injection vulnerability.

---

correction (per @CarpeDiem's comment):

INSERT into #T1 execute ('execute ' + @SQLString )

also, omit the 'execute' if the sql string is something other than a procedure

---

Related Topics