How can I insert dynamic sql into temp table?
20,404
Solution 1
I run this code and it returned me the test rows I'd created.
declare @query nvarchar(100)
set @query = N'select * into ##TMPTblTest from tblTest'
exec sp_executesql @query;
select * from ##TMPTblTest
You are using a global temporary table. If you make a select on it, I think it will work.
Solution 2
You can declare the temporary table struct outside dynamic sql, then you avoid to use global temporary table
if object_id('tempdb..#t1') is not null drop table #t1
create table #t1(ID int)
declare @s varchar(max)
set @s='insert into #t1(ID)select number from master.dbo.spt_values where type=''P'' and number<10'
exec(@s)
insert into #t1(id)
exec('Select 1')
select * from #t1
ID 1 0 2 1 3 2 4 3 5 4 6 5 7 6 8 7 9 8 10 9 11 1
Author by
Serdia
Updated on April 21, 2020Comments
-
Serdia about 4 years
I have this dynamic query, how can I insert the result of it into temp Table? The result of this query displays
(1000 row(s) affected)
But is any chance to dump those 1000 rows in a temp table?Something like that:
INSERT INTO #TempTable EXEC(@query)
Here is my query
DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX) SET @cols = STUFF((SELECT ',' + QUOTENAME(c.locationCode) FROM Catalytic_vw_LocationCodeByLine c WHERE c.linename ='wind' order by c.CompanyName, c.LocationCode FOR XML PATH('')),1,1,'') set @query = 'select * into ##Temp from (SELECT QUOTEGUID as qguid, ' + @cols + ' from ( select QuoteGUID, LocationCode, LineName, LineGUID from Catalytic_vw_PolicyLocationCode ) x pivot ( max(locationCode) for locationCode in (' + @cols + ') )p)x' EXEC sp_executesql @query;