Sql azure 中全局临时表的替代方案
我的 sp 的一部分包含以下代码。该代码在我的本地 SQL 数据库中成功执行。但是当我在 sql azure 中运行它时,它给出错误:
“此版本的 SQL Server 不支持全局临时对象。”
我如何更改此查询以使其在天蓝色中工作。
SET @query = N'SELECT STUDENT_ID, ROLL_NO, TITLE, STUDENT_NAME, EXAM_NAME, '+
@cols +
' INTO ##FINAL
FROM
(
SELECT ROLL_NO, TITLE, STUDENT_ID, SUBJECT_ID, STUDENT_NAME, EXAM_NAME, DISPLAYORDER, MARKS
FROM #AVERAGES
UNION
SELECT ROLL_NO, TITLE, STUDENT_ID, SUBJECT_ID, STUDENT_NAME, EXAM_NAME, DISPLAYORDER, MARKS
FROM #MARKS
UNION
SELECT ROLL_NO, TITLE, STUDENT_ID, SUBJECT_ID, STUDENT_NAME, EXAM_NAME, DISPLAYORDER, MARKS
FROM #GRACEMARKS
UNION
SELECT ROLL_NO, TITLE, STUDENT_ID, SUBJECT_ID, STUDENT_NAME, EXAM_NAME, DISPLAYORDER, MARKS
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)
A part of my sp contains following code . The code executes successfully in my local sql database . but when i run it in sql azure it gives the error saying:
"Global temp objects are not supported in this version of SQL Server."
How can i change this query to make it work in azure.
SET @query = N'SELECT STUDENT_ID, ROLL_NO, TITLE, STUDENT_NAME, EXAM_NAME, '+
@cols +
' INTO ##FINAL
FROM
(
SELECT ROLL_NO, TITLE, STUDENT_ID, SUBJECT_ID, STUDENT_NAME, EXAM_NAME, DISPLAYORDER, MARKS
FROM #AVERAGES
UNION
SELECT ROLL_NO, TITLE, STUDENT_ID, SUBJECT_ID, STUDENT_NAME, EXAM_NAME, DISPLAYORDER, MARKS
FROM #MARKS
UNION
SELECT ROLL_NO, TITLE, STUDENT_ID, SUBJECT_ID, STUDENT_NAME, EXAM_NAME, DISPLAYORDER, MARKS
FROM #GRACEMARKS
UNION
SELECT ROLL_NO, TITLE, STUDENT_ID, SUBJECT_ID, STUDENT_NAME, EXAM_NAME, DISPLAYORDER, MARKS
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)
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
迟到总比不到好。 Azure SQL DB 公共预览版中的数据库范围全局临时表:
并来自 MSDN
CREATE TABLE
:
Better late than never. Database Scoped Global Temporary Tables in public preview for Azure SQL DB:
And from MSDN
CREATE TABLE
: