在 SQL Azure 中使用临时表

Question

我正在编写一个查询来旋转表元素，其中列名称是动态生成的。

SET @query = N'SELECT STUDENT_ID, ROLL_NO, TITLE, STUDENT_NAME, EXAM_NAME, '+
            @cols +
            ' INTO ##FINAL 
            FROM
            (
                SELECT  *
                FROM #AVERAGES
                UNION 
                SELECT  *
                FROM #MARKS 
                UNION
                SELECT  *
                FROM #GRACEMARKS
                UNION
                SELECT  *
                FROM #TOTAL
                ) p
                PIVOT
                (
                MAX([MARKS])
                FOR SUBJECT_ID IN
                ( '+
                @cols +' )
            ) AS FINAL
            ORDER BY STUDENT_ID ASC, DISPLAYORDER ASC, EXAM_NAME ASC;'

EXECUTE(@query) 

select * from ##FINAL 

此查询在我的本地数据库中正常工作，但在 SQL Azure 中不起作用，因为那里不允许全局临时表。

现在，如果我在本地数据库中将 ##FINAL 更改为 #FINAL，但它会给我错误：

对象名称“#FINAL”无效。

我该如何解决这个问题？

Answer 1

好吧，说完我觉得不行，也许我还有办法。虽然它很丑。希望您可以使用下面的示例并将其适应您的查询（没有您的架构和数据，对我来说尝试编写它太棘手）：

declare @cols varchar(max)
set @cols = 'object_id,schema_id,parent_object_id'

--Create a temp table with the known columns
create table #Boris (
    ID int IDENTITY(1,1) not null
)
--Alter the temp table to add the varying columns. Thankfully, they're all ints.
--for unknown types, varchar(max) may be more appropriate, and will hopefully convert
declare @tempcols varchar(max)
set @tempcols = @cols
while LEN(@tempcols) > 0
begin
    declare @col varchar(max)
    set @col = CASE WHEN CHARINDEX(',',@tempcols) > 0 THEN SUBSTRING(@tempcols,1,CHARINDEX(',',@tempcols)-1) ELSE @tempcols END
    set @tempcols = CASE WHEN LEN(@col) = LEN(@tempcols) THEN '' ELSE SUBSTRING(@tempcols,LEN(@col)+2,10000000) END
    declare @sql1 varchar(max)
    set @sql1 = 'alter table #Boris add [' + @col + '] int null'
    exec (@sql1)
end

declare @sql varchar(max)
set @sql = 'insert into #Boris (' + @cols + ') select ' + @cols + ' from sys.objects'
exec (@sql)

select * from #Boris

drop table #Boris

它们的关键是在外部范围中创建临时表，然后内部作用域（在 EXEC 语句内运行的代码）可以访问相同的临时表。上面的代码适用于 SQL Server 2008，但我没有 Azure 实例可供使用，因此没有在那里进行测试。

Answer 2

如果您创建临时表，则它在 spid 中执行的动态 sql 中可见，如果您在动态 sql 中创建表，则它在外部不可见。

有一个解决方法。您可以创建存根表并在动态 sql 中更改它。它需要一些字符串操作，但我已经使用此技术为 tsqlunit 生成动态数据集。

CREATE TABLE #t1 
(
    DummyCol int
)

EXEC(N'ALTER TABLE #t1 ADD  foo INT')

EXEC ('insert into #t1(DummyCol, foo)
VALUES(1,2)')

EXEC ('ALTER TABLE #t1 DROP COLUMN DummyCol')

select *from #t1