CODEWITHSUNDEEP
sqlstructgoogle-bigqueryinsertnested-table
Dinesh
Dinesh

Reputation: 327

BigQuery Insert into Nested records

I have a table that has a nested record (please see attached snapshot).

schema defination

I am trying to do a simple insert but it's not working.

INSERT  INTO `my_project.my_dataset.test_table`(name,address,comments)
values( 
'my_name' as name,
  [STRUCT('ABC' as line1,'XYZ' as line2,10 as code),
   STRUCT('PQR' as line1,'STU' as line2,20 as code)],
   'Comment')

Any idea what's wrong with this SQL insert statement?

Thanks a lot for your help.

Upvotes: 4

Views: 9489

Answers (3)

vinay lokwani
vinay lokwani

Reputation: 1

Is there any way that I can enter data in address.code using insert statement. eg:

Something like that:

insert into `my_project.my_dataset.test_table`(name,address.code) select myname,[STRUCT('ABC')] from tab1

Upvotes: 0

Sergey Geron
Sergey Geron

Reputation: 10152

Consider changing address Mode to REPEATED:

enter image description here

Also as name needs to be removed:

INSERT  INTO `my_project.my_dataset.test_table`(name, address, comments)
values('my_name',
       [STRUCT('ABC','XYZ',10), STRUCT('PQR','STU',20)],
       'Comment'
      )

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269513

Don't assign names in values. Try:

INSERT  INTO `my_project.my_dataset.test_table`(name,address,comments)
    values('my_name',
           [STRUCT('ABC' as line1,'XYZ' as line2,10 as code),
            STRUCT('PQR' as line1,'STU' as line2,20 as code)],
           'Comment'
          )

Or use insert . . . select:

insert into `my_project.my_dataset.test_table`(name,address,comments)
    select 'my_name' as name,
           [STRUCT('ABC' as line1,'XYZ' as line2,10 as code),
            STRUCT('PQR' as line1,'STU' as line2,20 as code)],
           'Comment';

Upvotes: 6

Related Questions