8

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.

Community
  • 1
  • 1
Mudassir Hasan
  • 28,083
  • 20
  • 99
  • 133

5 Answers5

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]

Luke Girvin
  • 13,221
  • 9
  • 64
  • 84
Raj
  • 10,653
  • 2
  • 45
  • 52
  • is there any way of doing this without changing my original dynamic query ? Its too complicated – Mudassir Hasan Dec 21 '12 at 06:28
  • 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 before FROM – Raj Dec 21 '12 at 06:38
  • tried...its not working , is there a way to create table without knowing schema other than Select * into.. – Mudassir Hasan Dec 21 '12 at 08:05
  • It is not working is hardly enough. Post your dynamic SQL here – Raj Dec 21 '12 at 08:07
  • 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? – Campbeln Jun 05 '14 at 02:46
  • The reason temp table #temptable does not work, is because temporary tables are associated with the current SPID and sp_executesql creates a new sub process with its own ID. Global temp table ##temptable should work, which is not much different than creating a table in [tempdb]. You may want to use a more creative name to avoid collisions though. – Michael Erickson Mar 21 '21 at 19:30
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 .

Nipun Jain
  • 626
  • 4
  • 6
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
Richard Vivian
  • 1,700
  • 1
  • 14
  • 19
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)
Atheer Mostafa
  • 735
  • 3
  • 8
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
Arun Prasad E S
  • 9,489
  • 8
  • 74
  • 87