动态 SQL 结果 INTO 临时表 [英] Dynamic SQL Result INTO Temporary Table

问题描述

我需要将动态sql结果存入一个临时表#Temp.

I need to store dynamic sql result into a temporary table #Temp.

动态 SQL 查询结果来自 pivot 结果，因此列数不同(不固定).

Dynamic SQL Query result is from a pivot result, so number of columns varies(Not fixed).

SET @Sql = N'SELECT ' + @Cols + ' FROM 
        (
           SELECT ResourceKey, ResourceValue 
           FROM LocaleStringResources where StateId ='
+ LTRIM(RTRIM(@StateID)) + ' AND FormId =' + LTRIM(RTRIM(@FormID))
+ ' AND CultureCode =''' + LTRIM(RTRIM(@CultureCode)) + '''
         ) x
        pivot 
        (
            max(ResourceValue)
            for ResourceKey IN (' + @Cols + ')
        ) p ;'

     --@Cols => Column Names which varies in number

现在我必须将动态 sql 结果插入到 #Temp 表中，并将这个 #Temp 表与另一个现有表一起使用来执行连接或其他操作.

Now I have to insert dynamic sql result to #Temp Table and use this #Temp Table with another existing table to perform joins or something else.

(#Temp 表应该存在于那里以与其他现有表执行操作)

(#Temp table should exist there to perform operations with other existing tables)

如何将动态 SQL 查询结果插入到临时表中?

How can I Insert dynamic SQL query result To a Temporary table?

谢谢

推荐答案

你能试试下面的查询吗.

Can you please try the below query.

SET @Sql = N'SELECT ' + @Cols + ' 
    into ##TempTable
    FROM 
    (
       SELECT ResourceKey, ResourceValue 
       FROM LocaleStringResources where StateId ='
       + LTRIM(RTRIM(@StateID)) + ' AND FormId =' + LTRIM(RTRIM(@FormID))
       + ' AND CultureCode =''' + LTRIM(RTRIM(@CultureCode)) + '''
     ) x
    pivot 
    (
        max(ResourceValue)
        for ResourceKey IN (' + @Cols + ')
    ) p ;'

然后您可以使用 ##TempTable 进行进一步操作.

You can then use the ##TempTable for further operations.

但是，不要忘记在查询结束时删除 ##TempTable，因为如果您再次运行查询，它会报错，因为它是 全局临时表

However, do not forget to drop the ##TempTable at the end of your query as it will give you error if you run the query again as it is a Global Temporary Table

这篇关于动态 SQL 结果 INTO 临时表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！