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

Published 03-13-2019 06:17 PM 333 Views
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 )

%3CLINGO-SUB%20id%3D%22lingo-sub-368830%22%20slang%3D%22en-US%22%3ELesson%20Learned%20%2312%3A%20What%20types%20of%20temporary%20tables%20can%20I%20use%20in%20Azure%20SQL%20Datawarehouse%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-368830%22%20slang%3D%22en-US%22%3E%0A%20%26lt%3Bmeta%20http-equiv%3D%22Content-Type%22%20content%3D%22text%2Fhtml%3B%20charset%3DUTF-8%22%20%2F%26gt%3B%3CSTRONG%3EFirst%20published%20on%20MSDN%20on%20Dec%2030%2C%202016%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%20In%20SQL%20Datawarehouse%20we%20are%20able%20just%20to%20create%20tables%20with%20%23%2C%20so%2C%20that%20means%20that%20this%20temporal%20table%20will%20be%20available%20during%20the%20session%20that%20create%20this%20table.%20Other%20calls%2C%20for%20example%2C%20%23%23%20(%20Global%20Temporal%20Tables%20)%20or%20tempdb..name%20will%20return%20an%20syntax%20error%20like%20this%20one.%20URL%3A%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fsql-data-warehouse%2Fsql-data-warehouse-tables-temporary%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fsql-data-warehouse%2Fsql-data-warehouse-tables-temporary%3C%2FA%3E%20%3CBR%20%2F%3E%3CP%3ECREATE%20TABLE%20tempdb..Temporal(%5BOrderArticleId%5D%20%5Bint%5D%20NULL%2C%5BOrderId%5D%20%5Bint%5D%20NULL)%20WITH%20(DISTRIBUTION%20%3D%20ROUND_ROBIN%2CCLUSTERED%20COLUMNSTORE%20INDEX)%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F88898i68BE7CA55276FE43%22%20%2F%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECREATE%20TABLE%20%23%23Temporal%20(%5BOrderArticleId%5D%20%5Bint%5D%20NULL%2C%5BOrderId%5D%20%5Bint%5D%20NULL)%20WITH%20(DISTRIBUTION%20%3D%20ROUND_ROBIN%2CCLUSTERED%20COLUMNSTORE%20INDEX)%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F88899i09E85CECA601CCBE%22%20%2F%3E%3C%2FP%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20How%20to%20create%20a%20stored%20procedure%20to%20populate%20data%20of%20this%20temporal%20table.%20Please%2C%20follow%20up%20this%20script%20as%20example%20for%20your%20code.%20Please%2C%20remember%20to%20execute%20the%20creation%20of%20the%20temporal%20table%20in%20the%20same%20session.%20%3CB%3E%20%3CSTRONG%3E%20%3C%2FSTRONG%3E%20%3C%2FB%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CSTRONG%3E%20STEP%201%3A%20Create%20the%20temporal%20table.%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%3CP%3ECREATE%20TABLE%20%23Temporal%20(%5BOrderArticleId%5D%20%5Bint%5D%20NULL%2C%5BOrderId%5D%20%5Bint%5D%20NULL)%20WITH%20(DISTRIBUTION%20%3D%20ROUND_ROBIN%2CCLUSTERED%20COLUMNSTORE%20INDEX)%3C%2FP%3E%3CBR%20%2F%3E%20%3CSTRONG%3E%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CB%3E%20%3CSTRONG%3E%20STEP%202%3A%20Create%20the%20stored%20procedure%2C%20you%20could%20use%20the%20explicit%20transaction.%20%3C%2FSTRONG%3E%20%3C%2FB%3E%20%3CBR%20%2F%3E%3CP%3ECREATE%20PROCEDURE%20LoadTempTable%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EAS%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EBEGIN%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EDECLARE%20%40Value%20INT%20%3D%200%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESET%20NOCOUNT%20ON%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EBEGIN%20TRANSACTION%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EWHILE%20%40Value%20%26lt%3B100%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Ebegin%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Eset%20%40value%3D%40value%2B1%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EINSERT%20INTO%20%23Temporal%20(%5BOrderArticleId%5D%2C%5BOrderId%5D)%20values(%40Value%2C%40Value)%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Eend%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Ecommit%20transaction%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EEND%3C%2FP%3E%3CBR%20%2F%3E%20%3CSTRONG%3E%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CB%3E%20%3CSTRONG%3E%20STEP%203%3A%20Execute%20the%20stored%20procedure%20and%20read%20the%20results.%20%3C%2FSTRONG%3E%20%3C%2FB%3E%20%3CBR%20%2F%3E%3CP%3Eexec%20LoadTempTable%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Eselect%20*%20from%20%23temporal%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Edrop%20table%20%23temporal%20(%20When%20the%20session%20will%20close%20this%20table%20will%20be%20deleted%20too%20without%20running%20the%20drop%20command%20)%3C%2FP%3E%0A%20%0A%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-368830%22%20slang%3D%22en-US%22%3EFirst%20published%20on%20MSDN%20on%20Dec%2030%2C%202016%20In%20SQL%20Datawarehouse%20we%20are%20able%20just%20to%20create%20tables%20with%20%23%2C%20so%2C%20that%20means%20that%20this%20temporal%20table%20will%20be%20available%20during%20the%20session%20that%20create%20this%20table.%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-368830%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Eazure%20sql%20datawarehouse%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Eglobal%20temporal%20tables%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Etemporary%20tables%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Version history
Last update:
‎Mar 13 2019 06:17 PM
Updated by: