Insert result of executing dynamic query into a table

Question

I have a dynamic query @strQuery which on executing gives a result with lots of column. I want to insert the result from this dynamic query into a temporary table . I am doing this because I want to perform some filtering on the temporary table and get required result .

A similar question was asked on previous thread HERE in which a temporary table is created first and then data inserted using INSERT INTO.

I want to avoid this step due to long list of columns and also the datatypes of fields is not known to me.

select * into #tmh from
exec(@strQuery)

Error Message

Incorrect syntax near the keyword 'exec'.

How to do this ? Is it possible to be done in this way ? If not , please specify some other alternative to get store the result on executing dynamic query into a table. Thanks.

Answer 1

I have faced this situation before and here is what I did:

DECLARE @strQuery nVarchar(100)

SET @strQuery='SELECT * into [tempdb].[dbo].[temptable] FROM YourTable'

EXECUTE sp_executesql @strQuery

SELECT * FROM [tempdb].[dbo].[temptable]

DROP TABLE [tempdb].[dbo].[temptable]

It works fine. Don't ask me why a FQ table name and not #temptable. I have no idea. It does not work. The only way I could get it working was using [tempdb].[dbo].[temptable]

Answer 2

proceed like this

select t1.name,t1.lastname from(select * from table)t1.

where "select * from table" is your dyanmic query. which will return result which you can use as temp table t1 as given in example .