CTAS and temp db

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.