I'd like to insert data into a partitioned table (partitioned by ingestion time, hourly) from another table, but INSERT statement seems to not be supported in this case.

I tried : INSERT INTO table2 SELECT * FROM table1 but it doesn't work.

I searched around the web but couldn't find anything to help me regarding my issue. Could anybody enlighten me about this please ?

Thanks for your help!

Thibaut

CodePudding user response：

You probably need to specify the columns you are inserting into.

INSERT INTO table2 (col1,col2,...)
SELECT col1,col2,... FROM table1 
-- select * can work though if they have the exact same schema

See: https://cloud.google.com/bigquery/docs/using-dml-with-partitioned-tables#inserting_data_into_ingestion-time_partitioned_tables and https://stackoverflow.com/a/62632100/20010866