Search code examples
databaset-sqlazure-sql-databaseselect-into

Select * into #table from execute(@query)

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.

Solution

  • 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