How to insert data from CTE to a Temp Table?

Question

I am trying to create a some logic using CTE and then instead of using DML statement after CTE, I am trying to create a temp table using CTE. This is possible in T-SQL. Is it possible in GBQ?

I know I can create temp table instead of CTE in the below example, but just want to know the possibility!

WITH xyz AS
(SELECT * FROM table1)

CREATE TEMP TABLE temp1 AS (
SELECT * FROM xyz INNER JOIN table2 on ...);

Answer 1

Use below instead

CREATE TEMP TABLE temp1 AS (
  WITH xyz AS
  (SELECT * FROM table1)  
  SELECT * FROM xyz INNER JOIN table2 on ...
);

Answer 2

So in 2022 I believe that no longer works without a script or session in GBQ:

You could write your query as follows:

WITH xyz AS (
  SELECT 
    * 
  FROM table1
)  
SELECT 
  * 
FROM xyz 
INNER JOIN table2 
        ON ...

and then click the More Button -> Query Settings as shown below:

After that you can set a destination for your results a a temporary table and here you can define the name of your table etc. in your case it's temp1:

This way you can just save the results of your query into a temporary table. Hope it helps!