TSQL select into Temp table from dynamic sql

Question

This seems relatively simple, but apparently it's not.

I need to create a temp table based on an existing table via the select into syntax:

SELECT * INTO #TEMPTABLE FROM EXISTING_TABLE

The problem is, the existing table name is accepted via a parameter...

I can get the table's data via:

execute ('SELECT * FROM ' + @tableName)

but how do I marry the two so that I can put the results from the execute directly into the temp table.

The columns for each table that this is going to be used for are not the same so building the temp table before getting the data is not practical.

I'm open to any suggestions except using a global temp table.

Update:

This is completely ridiculous, BUT my reservations with the global temp table is that this is a multi user platform lends itself to issues if the table will linger for long periods of time...

Sooo.. just to get past this part I've started by using the execute to generate a global temp table.

execute('select * into ##globalDynamicFormTable from ' + @tsFormTable) 

I then use the global temp table to load the local temp table:

select * into #tempTable from ##globalDynamicFormTable

I then drop the global table.

drop table ##globalDynamicFormTable

this is dirty and I don't like it, but for the time being, until i get a better solution, its going to have to work.

In the End:

I guess there is no way to get around it.

The best answer appears to be either;

Create a view in the execute command and use that to load the local temp table in the stored procedure.

Create a global temp table in the execute command and use that to load the local temp table.

With that said i'll probably just stick with the global temp table because creating and dropping views is audited in my organization, and I'm sure they are going to question that if it starts happening all the time.

Thanks!

Answer 1

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

Answer 2

declare @sql varchar(100);

declare @tablename as varchar(100);

select @tablename = 'your_table_name';

create table #tmp 
    (col1 int, col2 int, col3 int);

set @sql = 'select aa, bb, cc from ' + @tablename;

insert into #tmp(col1, col2, col3) exec( @sql );

select * from #tmp;

Answer 3

How I did it with a pivot in dynamic sql (#AccPurch was created prior to this)

DECLARE @sql AS nvarchar(MAX)
declare @Month Nvarchar(1000)

--DROP TABLE #temp
select distinct YYYYMM into #temp from #AccPurch AS ap
SELECT  @Month = COALESCE(@Month, '') + '[' + CAST(YYYYMM AS VarChar(8)) + '],' FROM    #temp

SELECT   @Month= LEFT(@Month,len(@Month)-1)


SET @sql = N'SELECT UserID, '+ @Month + N' into ##final_Donovan_12345 FROM (
Select ap.AccPurch ,
       ap.YYYYMM ,
       ap.UserID ,
       ap.AccountNumber
FROM #AccPurch AS ap 
) p
Pivot (SUM(AccPurch) FOR YYYYMM IN ('+@Month+ N')) as pvt'


EXEC sp_executesql @sql

Select * INTO #final From ##final_Donovan_12345

DROP TABLE  ##final_Donovan_12345

Select * From #final AS f

Answer 4

DECLARE @count_ser_temp int;
DECLARE @TableName AS VARCHAR(100)
SELECT @TableName = 'TableTemporal'

EXECUTE ('CREATE VIEW vTemp AS
    SELECT *
    FROM ' + @TableTemporal)
SELECT TOP 1 * INTO #servicios_temp  FROM vTemp

DROP VIEW vTemp

-- Contar la cantidad de registros de la tabla temporal
SELECT @count_ser_temp = COUNT(*) FROM #servicios_temp;

-- Recorro los registros de la tabla temporal 
WHILE @count_ser_temp > 0
 BEGIN
 END
END

Answer 5

I ran into the same problem today. I'm not sure how many columns the executed query returned. So I created a temp table with a single column and changed it using a dynamic execute query.

I got the idea from c-sharpcorner.com. Instead of using a while loop, I used COALESCE to create a single comma-separated column.

Please feel free to make suggestions or ask questions.

-- Temp table you want to access after dynamic insert
drop table if exists #TempTable
create table #TempTable (ID int)

DECLARE @ColNames nvarchar(max), @DynamicSQL nvarchar(max)

-- query to create dynamic columns you need
SELECT @ColNames = COALESCE(@ColNames + ',', '') + QUOTENAME(COLUMN_NAME) + ' NVARCHAR(max) NULL'
from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'TABLE_NAME'

-- alter script to add column to temp table
SET @DynamicSQL = 'ALTER TABLE #TempTable ADD ' + @ColNames

-- add dynamic columns to temp table
EXEC (@DynamicSQL)

-- execute the query and result will be in #temptable
insert into #TempTable
execute('select 1 as ID,* from TABLE_NAME') 

select * from #TempTable

Answer 6

Take a look at OPENROWSET, and do something like:

SELECT * INTO #TEMPTABLE FROM OPENROWSET('SQLNCLI'
     , 'Server=(local)\SQL2008;Trusted_Connection=yes;',
     'SELECT * FROM ' + @tableName)