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 )