BigQuery Create table from query result

Create table as select (CTAS) is used to store the results of a query into a new table

Create table as select example

    CREATE TABLE mydataset.active_employee
    AS
    SELECT * FROM mydataset.employee where active_f = 'Y'

CTAS with partition and cluster key override

    CREATE TABLE mydataset.active_employee
    PARTITION BY DATE(timestamp)
    CLUSTER BY employee_id
    AS
    SELECT * FROM mydataset.employee where active_f = 'Y'

Create Table copying selected columns of another table

    CREATE TABLE mydataset.active_employee
    AS
    SELECT Id, Name FROM mydataset.employee where active_f = 'Y'

Explanation

Create table as select (CTAS) will create a new table with the results from the query. BigQuery will automatically create the table structure for you and store the results of the query

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]