1
EXECUTE(@QUERY)
  1. It Returns the Dynamic columns
  2. If i want to store data in Temporary table then i have to declare temp table first with columns and datatypes. but i dont know how many columns returns by EXECUTE(@QUERY).

so how to save dynamic output from EXECUTE(@QUERY) into temp table.

Er Ketan Vavadiya
  • 283
  • 3
  • 14

2 Answers2

1

You can do this with global temp table and select create statements in sql server

declare @QUERY nvarchar(500)
set @QUERY = 'select c1, c2, c3 from test_table'

declare @create_sql nvarchar(500)
set @create_sql = 'select * into ##temp_tbl from ('+ @QUERY + ') as x'
EXEC sp_executesql @create_sql

select * from ##temp_tbl

Here select * into ##temp_tbl will create ##temp table.

Praveen
  • 8,945
  • 4
  • 31
  • 49
  • It will not work if @Query is complicated. For example, if it has IF, SET, WHILE clauses or uses CTE or something like that – ventik Mar 15 '16 at 11:28
1

Here is solution with OpenRowSet. You only have to put all your work with temp table in dynamic sql (or you can use global temp table)

declare @Query nvarchar(max)
set @Query = 
'
select 1 as FirstColumn, ''Hello'' as SecondColumn, GetDate() as ThirdColumn
union
select 2 as FirstColumn, ''world'' as SecondColumn, GetDate() as ThirdColumn
'

execute(@Query)

declare @sql nvarchar(max)
set @sql = 

'
select * into #MyTempTable 
from OPENROWSET(''SQLNCLI'', ''Server=(local);Trusted_Connection=yes;'', '''+ Replace(@Query, '''', '''''') +''')

select * from #MyTempTable
'

exec sp_executeSQL @sql

-- global table example
set @sql = 

'
select * into ##MyTempTableGlobal 
from OPENROWSET(''SQLNCLI'', ''Server=(local);Trusted_Connection=yes;'', '''+ Replace(@Query, '''', '''''') +''')
'

exec sp_executeSQL @sql

select * from ##MyTempTableGlobal
ventik
  • 877
  • 7
  • 18