問題描述

我從我的上一個(gè)問題中有以下查詢，它在動態(tài)表中的 SQL.我怎樣才能把它傳到一張桌子上?我的意思是像 select * from sp_executesql @query.

I have the below query from my previous question and this executes in SQL in a dynamic table. How can i pass this into a table? I mean something like select * from sp_executesql @query.

我嘗試了 openrowset，但我的安全權(quán)限不允許.還有其他幫助嗎?

I tried openrowset but my security privilages do not allow it. Any other help?

謝謝，

Declare @cols as NVARCHAR(MAX), @query as NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(FIELD_NAME) 
                from bear_crossjoin
                group by Field_Name, FIELDNUMBER
                order by FIELDNUMBER
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'')

set @query = N'SELECT ' + @cols + N' 
            from 
            (
                select substring, Field_Name,
                  rn = row_number() over(partition by field_name order by fieldnumber)
                from bear_crossjoin
            ) x
            pivot 
            (
                max(substring)
                for Field_Name in (' + @cols + N')
            ) p ' 
exec sp_executesql @query

Print (@query) 將顯示列名

Print (@query) will display the column names

選擇[GRADE-BASIS-INDICATOR],[MOST-CURRENT-CODE],[PAY-PERIOD-NUMBER],[DATE-PROC-PP-BEGINS-CN],[DATE-PROC-PP-BEGINS-YR],[行動-代碼],[無人員-現(xiàn)金-獎]......一共1085個(gè)

SELECT [GRADE-BASIS-INDICATOR],[MOST-CURRENT-CODE],[PAY-PERIOD-NUMBER],[DATE-PROC-PP-BEGINS-CN],[DATE-PROC-PP-BEGINS-YR],[ACTION-CODE],[NO-PERSONS-CASH-AWARD]......there are 1085 of them

推薦答案

由于拉取動態(tài)字段列表，這需要作為一個(gè)SELECT {fields} INTO ##tmp FROM...code> 因?yàn)闆]有簡單的方法來獲得 CREATE TABLE 語句，特別是如果 bear_crossjoin 表中沒有列出的數(shù)據(jù)類型.因此，更新動態(tài) SQL 以增加一行，如下所示:

Due to pulling a dynamic field list, this kinda needs to be done as a SELECT {fields} INTO ##tmp FROM... since there is no easy way to get a CREATE TABLE statement, especially if the bear_crossjoin table does not have the datatypes listed in it. So, update the Dynamic SQL to have one extra line as follows:

set @query = N'SELECT ' + @cols + N' 
               INTO ##TempResults  -- add this one line!!
            from 
            (
...
exec sp_executesql @query

SELECT * FROM ##TempResults;

全局臨時(shí)表(即 ##name 而不是 #name)將在子進(jìn)程中創(chuàng)建后繼續(xù)存在.一張真正的桌子也能幸存下來.但是一旦子進(jìn)程(即動態(tài) SQL)結(jié)束，本地臨時(shí)表(即 #name 而不是 ##name)就會消失.

A Global Temp Table (i.e. ##name instead of #name) will survive being created in a sub-process. A real table would also survive. But a local temp table (i.e. #name instead of ##name) will disappear once the subprocess (i.e. the Dynamic SQL) ends.

這篇關(guān)于將 exec sp_executesql 結(jié)果加載到表中的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！