Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

using temp tables in SQL Azure

I am writing a query to pivoting table elements where column name is generated dynamically.

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 

This query works properly in my local database, but it doesn't work in SQL Azure since global temp tables are not allowed there.

Now if i change ##FINAL to #FINAL in my local database, but it gives me error as

Invalid object name '#FINAL' .

How can I resolve this issue?

like image 562
Kuntady Nithesh Avatar asked Aug 11 '11 06:08

Kuntady Nithesh


People also ask

Can we create temp tables in Azure SQL?

While creating a global temporary table, we use the prefix (##) before the table name in the CREATE TABLE statement. Now we will create a global temporary table in the Azure SQL database. Open the Azure portal in your browser, open the Query Editor for your database and login into the database.

Which is better CTE or temp table in SQL?

CTE has its uses - when data in the CTE is small and there is strong readability improvement as with the case in recursive tables. However, its performance is certainly no better than table variables and when one is dealing with very large tables, temporary tables significantly outperform CTE.

Which is better in performance CTE vs temp table?

Looking at SQL Profiler results from these queries (each were run 10 times and averages are below) we can see that the CTE just slightly outperforms both the temporary table and table variable queries when it comes to overall duration.

Does Azure synapse support temporal tables?

Unfortunately, we do not support this functionality in Azure Synapse Analytics.


2 Answers

Okay, after saying I didn't think it could be done, I might have a way. It's ugly though. Hopefully, you can play with the below sample and adapt it to your query (without having your schema and data, it's too tricky for me to attempt to write it):

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

They key is to create the temp table in the outer scope, and then inner scopes (code running within EXEC statements) have access to the same temp table. The above worked on SQL Server 2008, but I don't have an Azure instance to play with, so not tested there.

like image 192
Damien_The_Unbeliever Avatar answered Sep 18 '22 12:09

Damien_The_Unbeliever


If you create a temp table, it's visible from dynamic sql executed in your spid, if you create the table in dynamic sql, it's not visible outside of that.

There is a workaround. You can create a stub table and alter it in your dynamic sql. It requires a bit of string manipulation but I've used this technique to generate dynamic datasets for 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 
like image 33
Code Magician Avatar answered Sep 19 '22 12:09

Code Magician