Lesson Learned #12: What types of temporary tables can I use in Azure SQL Datawarehouse?

First published on MSDN on Dec 30, 2016
In SQL Datawarehouse we are able just to create tables with #, so, that means that this temporal table will be available during the session that create this table. Other calls, for example, ## ( Global Temporal Tables ) or tempdb..name will return an syntax error like this one. URL: https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-tables-temporary

CREATE TABLE tempdb..Temporal([OrderArticleId] [int] NULL,[OrderId] [int] NULL) WITH (DISTRIBUTION = ROUND_ROBIN,CLUSTERED COLUMNSTORE INDEX)

CREATE TABLE ##Temporal ([OrderArticleId] [int] NULL,[OrderId] [int] NULL) WITH (DISTRIBUTION = ROUND_ROBIN,CLUSTERED COLUMNSTORE INDEX)

How to create a stored procedure to populate data of this temporal table. Please, follow up this script as example for your code. Please, remember to execute the creation of the temporal table in the same session.



STEP 1: Create the temporal table.

CREATE TABLE #Temporal ([OrderArticleId] [int] NULL,[OrderId] [int] NULL) WITH (DISTRIBUTION = ROUND_ROBIN,CLUSTERED COLUMNSTORE INDEX)

STEP 2: Create the stored procedure, you could use the explicit transaction.

CREATE PROCEDURE LoadTempTable

AS

BEGIN

DECLARE @Value INT = 0;

SET NOCOUNT ON;

BEGIN TRANSACTION

WHILE @Value <100

begin

set @value=@value+1

INSERT INTO #Temporal ([OrderArticleId],[OrderId]) values(@Value,@Value)

end

commit transaction

END

STEP 3: Execute the stored procedure and read the results.

exec LoadTempTable

select * from #temporal

drop table #temporal ( When the session will close this table will be deleted too without running the drop command )