如何将动态 sql 插入临时表? [英] How can I insert dynamic sql into temp table?

问题描述

我有这个动态查询，如何将它的结果插入临时表?此查询的结果显示 (1000 row(s) affected)但是有没有机会将这 1000 行转储到临时表中?

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?

类似的东西:

INSERT INTO #TempTable
EXEC(@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;

推荐答案

我运行这段代码，它返回了我创建的测试行.

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.

这篇关于如何将动态 sql 插入临时表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！