CODEWITHSUNDEEP
sqlgoogle-bigquery
Zenvega
Zenvega

Reputation: 2054

selecting column names of one table from another table

I have 2 tables, table1 has 530 columns and table2 (has 421 columns) and all of table2's columns are there in table1.

How can I select only the columns of table2 from table1? I am doing this in Bigquery. BTW I searched for examples online, but I could not get a solution for this. Thank you.

Upvotes: 0

Views: 232

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173190

Use below approach

execute immediate 'select ' || 
(select string_agg(col, ',' order by offset)
from (select col, offset from (select * from table1 limit 1) t, unnest(`bqutil.fn.json_extract_keys`(to_json_string(t))) col with offset) 
join (select col from (select * from table2 limit 1) t, unnest(`bqutil.fn.json_extract_keys`(to_json_string(t))) col)
using(col) ) || ' from table1'

Upvotes: 1

Related Questions