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

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

CTAS with partition and cluster key override

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

Create Table copying selected columns of another table

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

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 ]\n[[project_name.]dataset_name.]table_name\n[(\n  column[, ...]\n)]\n[PARTITION BY partition_expression]\n[CLUSTER BY clustering_column_list]\n[OPTIONS(table_option_list)]\n[AS query_statement]