CODEWITHSUNDEEP
google-bigquery
Ziathes
Ziathes

Reputation: 15

How to INSERT data in Nested Table from Nested Table BigQuery

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 ?

Upvotes: 0

Views: 562

Answers (2)

Soumendra Mishra
Soumendra Mishra

Reputation: 3663

You can try this:

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

Upvotes: 1

DragonMaster
DragonMaster

Reputation: 81

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

Upvotes: 0

Related Questions