How can I insert dynamic sql into temp table?

Question

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;

http://stackoverflow.com/a/1228165/6167855 and the answer just below it. For the first, you'll have to use concatenation. — S3S, Apr 20 '17 at 18:39
If you want to use temp tables, you need to create the temp table outside the dynamic SQL. — EMUEVIL, Apr 20 '17 at 18:43
What would be my `Provider Name` if I have `Microsoft SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (X64) Oct 20 2015 15:36:27 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.2 (Build 9200: )` — Serdia, Apr 20 '17 at 18:56

score 10 · Accepted Answer · answered Apr 20 '17 at 20:25

10

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.

answered Apr 20 '17 at 20:25

regisls

529
6
23

Works fine! Thank you – Serdia Apr 20 '17 at 23:38

score 7 · Answer 2 · answered Apr 20 '17 at 20:42

7

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

answered Apr 20 '17 at 20:42

Nolan Shang

2,312
1
14
10

This is a straightforward proof that a temp table can receive data from dynamic SQL in the same script. Nice touch adding the second way to insert from dynamic SQL. (Added comment above it which hopefully clarifies it for future viewers. – yeOldeDataSmythe Sep 02 '21 at 14:44

How can I insert dynamic sql into temp table?

2 Answers2

Linked