BigQuery - Using INSERT INTO to copy data from one nested table into another nested table
24,481
Answer below. Hope this helps someone else out too!
INSERT INTO
`<GCP_PROJECT_NAME>.Test_Tables.Nested_Person_Table2`
(id,
first_name,
last_name,
dob,
addresses)
SELECT
id,
first_name,
last_name,
dob,
ARRAY_AGG(STRUCT(a1.status,
a1.address,
a1.city,
a1.state,
a1.zip,
a1.numberOfYears)) AS addresses
FROM
`<GCP_PROJECT_NAME>.Test_Tables.Nested_Person_Table`,
UNNEST(addresses) AS a1
GROUP BY
id,
first_name,
last_name,
dob
Related videos on Youtube
Author by
Nick Orlove
Updated on June 14, 2020Comments
-
Nick Orlove almost 4 years
Helping a customer out. I'm trying to copy one nested BigQuery table into another nested table and am running into the following error: "Syntax error: Expected ")" or "," but got ".""
Query:
INSERT INTO `<GCP_PROJECT_NAME>.Test_Tables.Nested_Person_Table2` (id, first_name, last_name, dob, address.status, address.address, address.city, address.state, address.zip, address.numberOfYears) SELECT id, first_name, last_name, dob, address.status, address.address, address.city, address.state, address.zip, address.numberOfYears FROM `<GCP_PROJECT_NAME>.Test_Tables.Nested_Person_Table`