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 ##FINA
L to #FINAL
in my local database, but it gives me error as
Invalid object name '#FINAL' .
How can I resolve this issue?
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.
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.
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.
Unfortunately, we do not support this functionality in Azure Synapse Analytics.
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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With