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.

Luke Girvin · Accepted Answer · 2018-12-10 16:30:19Z

15

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]

edited Dec 10, 2018 at 16:30

Luke Girvin

13.5k10 gold badges69 silver badges87 bronze badges

answered Dec 21, 2012 at 6:24

Raj

10.9k2 gold badges47 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Mudassir Hasan Over a year ago

is there any way of doing this without changing my original dynamic query ? Its too complicated

Raj Over a year ago

I tried a lot of ways, but could not find a working solution to create the temp table on the fly using SELECT INTO. There would not be too much change to the dynamic SQL. You are just going to add "into <TableName> before FROM

Mudassir Hasan Over a year ago

tried...its not working , is there a way to create table without knowing schema other than Select * into..

Raj Over a year ago

It is not working is hardly enough. Post your dynamic SQL here

Campbeln Over a year ago

This works fine for me! But does creating the temp table in this way change how it behaves (e.g. will concurrent connections have collisions) or is it still limited by the current scope?

|

Nipun Jain · Accepted Answer · 2012-12-21 05:57:28Z

1

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 .

answered Dec 21, 2012 at 5:57

Nipun Jain

6014 silver badges6 bronze badges

Comments

Richard Vivian · Accepted Answer · 2012-12-21 08:13:15Z

1

You can use variables in your current execution context, set by the 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

answered Dec 21, 2012 at 8:13

Richard Vivian

1,7701 gold badge14 silver badges19 bronze badges

Comments

Atheer Mostafa · Accepted Answer · 2012-12-27 10:40:56Z

1

Yes you can make a new dynamic query containing the original query with the insert like this:

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

answered Dec 27, 2012 at 10:40

Atheer Mostafa

7353 silver badges8 bronze badges

Comments

Arun Prasad E S · Accepted Answer · 2016-01-02 13:11:02Z

0

I used this to work around - with out dynamic query

This uses a table variable to receive data to 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

answered Jan 2, 2016 at 13:11

Arun Prasad E S

10.2k8 gold badges81 silver badges94 bronze badges

Collectives™ on Stack Overflow

Insert result of executing dynamic query into a table

5 Answers 5

6 Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

6 Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related