BigQuery Create temporary table

Temporary tables let you save intermediate results to a table. These temporary tables exist at the session level, so you don't need to save or maintain them in a dataset. They are automatically deleted some time after the script completes. NOTE: Temp tables are only accessible in the current session that created it, which makes it's use restricted to only scripting and routines (stored procedures). Its not suited for staging data during ETL

Create temp table example

    CREATE TEMP TABLE Example
    (
      x INT64,
      y STRING
    );
    
    INSERT INTO Example
    VALUES (5, 'foo');
    
    INSERT INTO Example
    VALUES (6, 'bar');
    
    SELECT * FROM Example;

Example 2:

    CREATE TEMP TABLE top_names(name STRING)
    AS
     SELECT name FROM `bigquery-public-data`.usa_names.usa_1910_current
     WHERE year = 2017
    ;

    SELECT name FROM top_names

Example 3: specifying optional _SESSION qualifier

    CREATE TEMP TABLE _SESSION.top_names(name STRING)
    AS
     SELECT name FROM `bigquery-public-data`.usa_names.usa_1910_current
     WHERE year = 2017
    ;
    
    SELECT * FROM _SESSION.top_names;

Explanation

While creating a temporary table, don't use a project or dataset qualifier in the table name. The table is automatically created in a special dataset.

You can refer to a temporary table by name for the duration of the current script. This includes temporary tables created by a procedure within the script. You cannot share temporary tables, and they are not visible using any of the standard list or other table manipulation methods.

After a script finishes, the temporary table exists for up to 24 hours. It is not saved using the name you gave it, however, but is assigned a random name instead. To view the table structure and data, go to the BigQuery console, click Query history, and choose the query that created the temporary table. Then, in the Destination table row, click Temporary table. You are not charged for storing temporary tables.

Syntax reference

CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] TABLE [ IF NOT EXISTS ]
[[project_name.]dataset_name.]table_name
[(
  column[, ...]
)]
[PARTITION BY partition_expression]
[CLUSTER BY clustering_column_list]
[OPTIONS(table_option_list)]
[AS query_statement]