Home » Bigquery » Tutorials » Create table like

BigQuery Create table like

Create table like statement creates an empty table copying the structure from another table. By default, the new table inherits partitioning, clustering, and options metadata from the source table

Create table like example

CREATE TABLE mydataset.New_Employee\n    LIKE mydataset.Employee
\n\n

Create table like override partitioning

\n
CREATE TABLE mydataset.New_Employee\n    LIKE mydataset.Employee\n    Partition by Join_Date\n    Cluster by Id, dept\n
\n\n

Create table like with data

\n
CREATE TABLE mydataset.New_Employee\n    LIKE mydataset.Employee\n    Partition by Join_Date\n    Cluster by Id, dept\n    as \n    select * from mydataset.Staff;\n

Explanation

The CREATE TABLE LIKE statement copies only the metadata of the source table. You can use the as query_statement clause to include data into the new table.

Syntax reference

{CREATE TABLE | CREATE TABLE IF NOT EXISTS | CREATE OR REPLACE TABLE}\n[[project_name.]dataset_name.]table_name\nLIKE [[project_name.]dataset_name.]source_table_name\n[PARTITION BY partition_expression]\n[CLUSTER BY clustering_column_list]\n[OPTIONS(table_option_list)]\n[AS query_statement]