How to INSERT data in Nested Table from Nested Table BigQuery

Question

I have 2 Nested Tables and I want to INSERT INTO each other.

I try this :

INSERT INTO table1 ( record.id, record.product.type, record.product.price )
SELECT
 id
 product.type
 product.price
FROM table 2

or this :

INSERT INTO table1 ( record.id, record.product )
VALUES (
      STRUCT((select id from table2)),
      STRUCT((select product.type from table2))
  )

BQ alert :

Syntax error: Expected ")" or "," but got "." at [1:62] Learn More about BigQuery SQL Functions.

But don't works ..

table 1

record                      RECORD  NULLABLE    
record.id                   STRING  NULLABLE    
record.product              RECORD  NULLABLE    
record.product.type         STRING  NULLABLE    
record.product.price        FLOAT   NULLABLE

table 2

id                      STRING  NULLABLE    
product                 RECORD  NULLABLE    
product.type            STRING  NULLABLE    
product.price           FLOAT   NULLABLE

How can do that ?

Soumendra Mishra · Accepted Answer · 2020-10-26 18:16:27Z

1

You can try this:

INSERT INTO table1
SELECT STRUCT(id, STRUCT(productPrice.type, productPrice.price)) FROM table2;

answered Oct 26, 2020 at 18:16

Soumendra Mishra

3,7131 gold badge18 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Soumendra Mishra Over a year ago

Please consider voting up and accepting this answer if it helped !

Amirhossein Saffari · Accepted Answer · 2020-10-26 11:18:30Z

0

you can use the APPLY operator to unpivot a table please visit this answer https://stackoverflow.com/a/27708046/1862306

answered Oct 26, 2020 at 11:18

Amirhossein Saffari

811 silver badge3 bronze badges

Collectives™ on Stack Overflow

How to INSERT data in Nested Table from Nested Table BigQuery

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related