2

I have created a dynamic query which returns the number of columns from table as: 

set @query = 'select '+@cols+' from [Sample] '

Now I want to fill a temporary table by executing this query, When I try this 

select * into #table from execute(@query).

I get following error:

Incorrect syntax near the keyword 'execute'

And

Incorrect syntax near ')'

However running this command returns the result accurately : execute(@query)

Note: I have tried OPENROWSET which is not supported in sql-azure.

Please help if there is any other work around.

Roman Marusyk
  • 23,328
  • 24
  • 73
  • 116
Wajid Khilji
  • 33
  • 8
  • 1
    The syntax would be `insert #table execute(@query)` however this requires `#table` to already exist, which means you must know the correct column definitions inline in order to create it, which presumably is not the case. – Alex K. Oct 13 '15 at 14:38

1 Answers1

1

Try to use FQ table name and not #temptable: 

 IF object_id('tempdb..temptable') IS NOT NULL DROP TABLE [tempdb].[dbo].[temptable] 
 DECLARE @query varchar(4000)
 SET @query = 'select '+ @cols +' into [tempdb].[dbo].[temptable] from [Sample]'

 EXECUTE (@query)

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

Please see the result in SQLFiddle

Roman Marusyk
  • 23,328
  • 24
  • 73
  • 116