Big Query - Create a Table/View from a Temp Table

I am not able to create temporary table in Bigquery

As mentioned by @Jaytiger names are not included in the CREATE TEMP TABLE statement.

You can create temporary table to store the results of a query as follows:

CREATE OR REPLACE TEMP TABLE <table1> as (SELECT * FROM `<dataset>.<table2>`);
SELECT * from <table1>

You can follow this documentation for further examples on Temp tables.

temp tables faster than variable tables big query

Temporary tables give flexibility to make customized tables for data visualization, as per the analytics requirements. More so, the use-case of TEMP is in the local temporary tables, only visible to the current session.

Meanwhile, the WITH clause acts as a temporary table, but it is actually a result of a subquery which can be used somewhere else.

The time difference that you get is because temporary tables use cache query results. This means that the query values are stored in the cache memory. That’s why it is faster to execute than the queries with the WITH clause. Sometimes, when you run a duplicate query, BigQuery attempts to reuse cached results.

How to insert data from CTE to a Temp Table?

Use below instead

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

BigQuery: Insert into temporary table

CREATE TEMP TABLE myNewTempTable AS 
SELECT * 
FROM `project.dataset.myTable` 

How to create a Temporary table in Bigquery

As suggested by @Paul, You get this error because a CTE (i.e. the WITH statement) is only part of a query. It needs to be followed by another statement, usually a SELECT. You can also refer to this StackOverflow question where the same error has been discussed.

You can try the below code which I have modified accordingly to resolve the error.

WITH analysis_fall_2021_season AS
(SELECT
start_station_name,
end_station_name,
EXTRACT (DATE FROM started_at) AS start_date,
EXTRACT (DATE FROM ended_at) AS end_date,
EXTRACT (TIME FROM started_at) AS start_time,
EXTRACT (TIME FROM ended_at) AS end_time,
DATETIME_DIFF (ended_at,started_at, MINUTE) AS total_lenght,
member_casual
FROM
(SELECT
fall_analysis.ride_id,
fall_analysis.started_at,
fall_analysis.ended_at,
fall_analysis.start_station_name,
fall_analysis.end_station_name,
fall_analysis.member_casual
FROM
`ciclystic.cyclistic_seasonal_analysis.fall_202010` AS fall_analysis
INNER JOIN
`ciclystic.cyclistic_seasonal_analysis.fall_202011` AS fall_202011
ON
fall_analysis.member_casual = fall_202011.member_casual
INNER JOIN
`ciclystic.cyclistic_seasonal_analysis.fall_202012` AS fall_202012
ON
fall_analysis.member_casual = fall_202012.member_casual)
)
SELECT * FROM analysis_fall_2021_season;

---

Related Topics