BQ - INSERT without listing columns

Question

I have 2 BQ tables, very wide ones in terms of number of columns. Note all the table columns are made nullable for flexibility

Table A - 1000 cols - Superset of Bs cols Table B - 500 cols - Subset of As cols - exactly named/typed as above cols

So rows in Bs table data should be insertable into A, where anything column not inserted just gets a null. i.e 500 cols get a value, remaining 500 get a default null as not present in the insert.

So as these tables are very wide, enumerating all the columns in an insert statement would take forever and be a maintence nightmare.

Is there a way in standard SQL to insert without listing the columns names in the the insert statement, whereby its automagically name matched?

So I want to be able to do this really and have the columns from B matched to A for each row inserted? If not is there any other way I am not seeing that could help with this?

thanks!

INSERT INTO
  `p.d.A` (
  SELECT
    *
  FROM
    `p.d.B` )

I actually tried enumerating the columns to see if nesting worked and seems it doesnt?

INSERT INTO
      `p.d.A` (x, y.z) (
      SELECT
        x, y.z
      FROM
        `p.d.B` )

I cant just say (x,y) as y structs from the dff tables arent exactly the same BQ complains structs dont match exact.....hence why I was trying y.z ?

Answer 1

Sure, easy!

Prepare dummy table p.d.b_ using below select

SELECT * FROM `p.d.a` WHERE FALSE   

(Note, even though result will be empty table - above will scan whole table a - this is required just once - so should be Okey - if not you can script this once and just create this table from script)

Ok, so now instead of using

SELECT * FROM `p.d.b`  

you will use

SELECT * FROM `p.d.b*`  

and this will make a trick for you (it did for me :o)

P.S. Of course I assume you will make sure there is no other tables with names starting with b (or whatever real name is) in that dataset