CODEWITHSUNDEEP
sql-serversql-server-2008
Er Ketan Vavadiya
Er Ketan Vavadiya

Reputation: 283

how to store data in Temp Table from EXECUTE(@QUERY) where EXECUTE(@QUERY) Returns the Dynamic Table

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.

Upvotes: 1

Views: 771

Answers (2)

ventik
ventik

Reputation: 887

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

Upvotes: 1

Praveen
Praveen

Reputation: 9335

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.

Upvotes: 1

Related Questions