TSQL select into Temp table from dynamic sql
Solution 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
Solution 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;
Patrick
Updated on July 09, 2022Comments
-
Patrick almost 2 years
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!
-
Patrick over 12 yearsthere are pemissions issues that keep open rowset from working, not to mention that with 3 different environments, id have to make sure that every time this solution is deployed that its using the correct connection string for the openrowset.
-
Kaf over 12 yearsSecond SELECT in after the INSERT is to show that you could query the data just inserted into the #TEMP table.
-
Patrick over 12 yearsbut creating the temp table in the execute puts the temp table completely out of scope... try doing a select from the temp table outside of the execute command... it won't work.
-
Patrick over 12 yearsdeclare vartablename as varchar(100) select vartablename = 'collateral' execute ('select * into #temp from ' + vartablename) select * from #temp results in - Invalid object name '#temp'.
-
Kaf over 12 yearsYes, I understand that "Invalid object name " issue if you use it out of the execute command. You can query #TEMP within the execute string as I did. Not sure what you need to do after filling the temp table..
-
Patrick over 12 years@RajMore of course it works, but it doesnt work for what I need it for. it puts the temp table out of scope. as you can see from my 2nd response to the original comment.
-
Patrick over 12 years@Indikaf I'm going to be inserting values into the temp table based on the business process in the stored procedure from a list of values sent in as a string, and then ill be using the temp table to insert back into the original table.
-
Kaf over 12 yearsIs inserting back into original table happening in the same stored procedure? Can you give us an example of what you do after filling temp table?
-
Patrick over 12 yearsyes, it is going to happen in the same stored procedure. the temp table is being retrieved schema only. once the temp table is built, i split out the deliminated values that were created app using a dictionary. the dictionary contains key value pairs that have the column for the table, and the value for the column. i'm then building an instert statment using those columns/values to be inserted into the temp table to do additional work before inserting back into the original table
-
Kaf about 12 yearsOK here we go again. This is again working and you can access the temp table as you wish. EXECUTE ('CREATE VIEW vTemp AS SELECT * FROM ' + @YourTableName) SELECT * INTO #TEMP FROM vTemp --DROP THE VIEW HERE DROP VIEW vTemp /*DO WHAT YOU NEED HERE ************************/ --EX: SELECT * FROM #TEMP /*END OF YOUR JOB */ --DROP YOUR TEMP TABLE HERE DROP TABLE #TEMP
-
Patrick about 12 yearsi see. its pretty much the same thing as creating a global temp table and loading it from there... i think the moral of the story is... you can't make a temp table from a dynamic select w/o getting your hands dirty. +1 for sticking with it and coming up with a viable answer.
-
sianabanana over 8 yearsThis doesnt work - you cannot pass variables to OpenRowset
-
dstandish over 7 yearsIt had an error (need parentheses around @sql), and did not create the sample table to select from, but the general approach does in fact work. I have corrected these issues.