CTAS and temp db

[smpa01]

I am having issues when I am trying to use CTAS in a tempdb.

This works

// no ctas
IF OBJECT_ID('tempdb..#test', 'U') IS NOT NULL
    DROP TABLE #test;

-- Create table
CREATE TABLE #test (
    Dates DATE
);

-- Insert data into table
INSERT INTO #test (Dates)
VALUES ('2017-01-01'), ('2017-04-14'), ('2017-04-17');

select * from #test

This does not

// with ctas 
//fact_sales is an exisitng table
IF OBJECT_ID('tempdb..#test', 'U') IS NOT NULL
    DROP TABLE #test;

create table #test(cust_id varchar(100))

SELECT cust_id
INTO #test
FROM [staging_lakehouse].[dbo].[fact_sales]
GROUP BY cust_id;

-- Select from the temporary table
SELECT * FROM #test;

Also, insert into select does not work

// with ctas 
//fact_sales is an exisitng table
IF OBJECT_ID('tempdb..#test', 'U') IS NOT NULL
    DROP TABLE #test;

create table #test(cust_id varchar(100))

insert into #test (cust_id)
SELECT cust_id
FROM [staging_lakehouse].[dbo].[fact_sales]
GROUP BY cust_id;

-- Select from the temporary table
SELECT * FROM #test;

The query references an object that is not supported in distributed processing mode.

[AndyDDC · Accepted solution]

Hi @smpa01 as per MS documentation, temp tables are not officially supported (even though they are present...).  MS are looking into the usage scenarios of temp tables currently. 

https://learn.microsoft.com/en-us/fabric/data-warehouse/tsql-surface-area

I have a blog which covers what does and doesn't work with the unsupported temp tables in Fabric.

https://www.serverlesssql.com/temp-tables-in-fabric-warehouses/