How do I insert a record with a RECORD field in BigQuery using DML syntax?

user_1549741 0
Viewed 4738

I have this field which is a Record type with 3 sub-fields in a table (let's say table A). I want to copy to this table from another table (let's say table B) where these 3 fields are separate fields in BigQuery:

Table A:

env       Record
env.temp  INTEGER
env.ts    TIMESTAMP
env.desc  STRING

Table B:

env_temp  INTEGER
env_ts    TIMESTAMP
env_desc  STRING

I have no idea how to construct INSERT SQL statement in particular for this Record field (env): 

INSERT table_A (env) SELECT ???? FROM table_B

Please enlighten me !

google-bigquery
1 Answers

You need to make a STRUCT, e.g.:

INSERT table_A (env)
SELECT STRUCT(env_temp AS temp, env_ts AS ts, env_desc AS desc) AS env
FROM table_B
user_62533470
Related