Insert the result of a dynamic query into a table

Question

I have a dynamic @strQuery query @strQuery , when executed, produces a result with lots of columns. I want to insert the result from this dynamic query into a temporary table. I do this because I want to do some filtering in a temporary table and get the desired result.

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

I want to avoid this step due to the long list of columns, as well as data types that are unknown to me.

 select * into #tmh from exec(@strQuery) 

Error message

Incorrect syntax next to the keyword "exec".

How to do it? Can this be done in this way? If not, specify another alternative to get the result when executing a dynamic query in the table. Thanks.

Answer 1

I came across 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 great. Do not ask me why the name of the table is FQ and not #temptable. I have no idea. This does not work. The only way to make it work is to use [tempdb]. [Dbo]. [Seductive]

Rajah

Answer 2

proceed as follows

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

where "select * from table" is your diamond query. which will return a result that you can use as a temporary table t1, as indicated in the example.

Answer 3

You can use variables in the current execution context defined by dynamic SQL with the OUTPUT option. Sample code below.

 DECLARE @Amount AS MONEY DECLARE @SQL AS NVARCHAR(1000) SET @Amount = NULL SET @SQL = ('SELECT @amt=100' ) EXECUTE sp_executeSQL @SQL, N'@amt MONEY OUTPUT', @ amt=@Amount OUTPUT SELECT @Amount 

Answer 4

Yes, you can create a new dynamic query containing the original query with an insert, like this:

 declare @strNewQuery varchar(max) set @strNewQuery ='select * into #tmh from (' +@strQuery +') as t' exec(@strNewQuery) 

Answer 5

I used this to work - no dynamic query

This uses a table variable to receive data into the procedure.

Even joins can be applied to it.

 select * into #itemPhantom from @tbl_items_upload select * from #itemPhantom select #itemPhantom.itemreference from #itemPhantom left join phantom on phantom.name=#itemPhantom.PhantomName