best way to append +30 tables using sql bigquery

I have 30+ tables, all with different #’s of columns.

I’m hoping to append all the tables together into 1 single table that would share the same column names

i’m having trouble with the query below. I’ve essentially selected the column names i want (but not all appear in each table)

SELECT vertical,opportunity_name, project_name_, campaign_id, agency, account_name, sub_brand, record, uuid, date, status, q1r1, q1r2, q1r3, q1r4, q1r5, q1r6, q1r7, q1r99, q2, q3, q4, q5, q6a, q6b, q7a, q7b, q8a, q8b, q9, q10, cs1, cs1r1, cs1r2, cs1r3, cs1r4, cs1r5, cs1r6, cs1r7, cs1r8, cs1r9, cs1r10, cs1r11, cs1r12, cs1r13, cs2, cs2r1, cs2r2, cs2r3, cs2r4, cs2r5, cs2r6, cs2r7, cs2r8, cs2r9, cs2r10, cs3, cs3r1, cs3r2, cs3r3, cs3r4, cs3r5, cs3r6, cs3r7, cs3r8, cs3r9, cs3r10, cs4, cs4r1, cs4r2, cs4r3, cs4r4, cs4r5, cs4r6, cs4r7, cs5, cs5r1, cs5r2, cs5r3, cs5r4, cs5r5, cs5r6, cs5r7, cs6, cs6r1, cs6r2, cs6r3, cs6r4, cs6r5, q11, q12r1, q12r2, q12r3, q12r4, q12r5, q12r6, q13r1, q13r2, q13r3, q13r4, q13r5, q13r6, q14r1, q14r2, q14r3, q14r4, q14r5, q14r6, q15r1, q15r2, q15r3, q15r4, q15r5, q15r6, q16r1, q16r2, q16r3, q16r4, q16r5, q16r6, q17, q17newr1, q17newr2, q17newr3, q17newr4, q17newr5, q17newr6, q17newr7, q18newr1, q18newr2, q18newr3, q18newr4, q18newr5, q18newr6, q18new99, q19newr1, q19newr2, q19newr3, q19newr4, q19newr5, q19newr6, q19newr7, q19newr8, q19newr9, q19newr99, q19r1, q19r2, q19r3, q19r4, q19r5, q19r6, q19r99, q20, q21, q22, q23, q24r1, q24r2, q24r3, q24r4, q24r5, q24r6, q25, q28r1, q28r2, q28r3, q28r4, q28r5, q2901r1, q2901r2, q2901r3, q2901r4, q2901r5, q2901r6, q2901r7, q2901r8, q2901r9, q2901r10, q2901r11, q2901r12, q2901r13, q2901r14, q2901r97, q29a01, q3001r1, q3001r2, q3001r3, q3001r4, q3001r5, q3001r6, q3001r7, q3001r8, q3001r9, q3001r10, q3001r11, q3001r97, q30a01, q3101r1, q3101r2, q3101r3, q3101r4, q3101r5, q3101r6, q3201r1, q3201r2, q3201r3, q3201r4, q3201r5, q2902r1, q2902r2, q2902r3, q2902r4, q2902r5, q2902r6, q2902r7, q2902r8, q2902r9, q2902r10, q2902r11, q2902r12, q2902r13, q2902r14, q2902r97, q29a02, q3002r1, q3002r2, q3002r3, q3002r4, q3002r5, q3002r6, q3002r7, q3002r8, q3002r9, q3002r10, q3002r11, q3002r97, q30a02, q3102r1, q3102r2, q3102r3, q3102r4, q3102r5, q3102r6, q3202r1, q3202r2, q3202r3, q3202r4, q3202r5, q2903r1, q2903r2, q2903r3, q2903r4, q2903r5, q2903r6, q2903r7, q2903r8, q2903r9, q2903r10, q2903r11, q2903r12, q2903r13, q2903r14, q2903r97, q29a03, q3003r1, q3003r2, q3003r3, q3003r4, q3003r5, q3003r6, q3003r7, q3003r8, q3003r9, q3003r10, q3003r11, q3003r97, q30a03, q3103r1, q3103r2, q3103r3, q3103r4, q3103r5, q3103r6, q3203r1, q3203r2, q3203r3, q3203r4, q3203r5, q2904r1, q2904r2, q2904r3, q2904r4, q2904r5, q2904r6, q2904r7, q2904r8, q2904r9, q2904r10, q2904r11, q2904r12, q2904r13, q2904r14, q2904r97, q29a04, q3004r1, q3004r2, q3004r3, q3004r4, q3004r5, q3004r6, q3004r7, q3004r8, q3004r9, q3004r10, q3004r11, q3004r97, q30a04, q3104r1, q3104r2, q3104r3, q3104r4, q3104r5, q3104r6, q3204r1, q3204r2, q3204r3, q3204r4, q3204r5, q2905r1, q2905r2, q2905r3, q2905r4, q2905r5, q2905r6, q2905r7, q2905r8, q2905r9, q2905r10, q2905r11, q2905r12, q2905r13, q2905r14, q2905r97, q29a05, q3005r1, q3005r2, q3005r3, q3005r4, q3005r5, q3005r6, q3005r7, q3005r8, q3005r9, q3005r10, q3005r11, q3005r97, q30a05, q3105r1, q3105r2, q3105r3, q3105r4, q3105r5, q3105r6, q3205r1, q3205r2, q3205r3, q3205r4, q3205r5, q33r1, q33r2, q33r3, q33r4, q33r5, q33r99, q34r1, q34r2, q34r3, q34r4, q34r5, q35, q36, q37r1, q37r2, q37r3, q37r4, q37r5, q37r6, q38r1, q38r2, q38r3, q38r4, q38r5, q38r6, q39r1, q39r2, q39r3, q39r4, q39r5, q39r6, q40r1, q40r2, q40r3, q40r4, q40r5, q40r6, q41r1, q41r2, q41r3, q41r4, q41r5, q41r6, from Table 1
UNION ALL 
SELECT vertical,opportunity_name, project_name_, campaign_id, agency, account_name, sub_brand, record, uuid, date, status, q1r1, q1r2, q1r3, q1r4, q1r5, q1r6, q1r7, q1r99, q2, q3, q4, q5, q6a, q6b, q7a, q7b, q8a, q8b, q9, q10, cs1, cs1r1, cs1r2, cs1r3, cs1r4, cs1r5, cs1r6, cs1r7, cs1r8, cs1r9, cs1r10, cs1r11, cs1r12, cs1r13, cs2, cs2r1, cs2r2, cs2r3, cs2r4, cs2r5, cs2r6, cs2r7, cs2r8, cs2r9, cs2r10, cs3, cs3r1, cs3r2, cs3r3, cs3r4, cs3r5, cs3r6, cs3r7, cs3r8, cs3r9, cs3r10, cs4, cs4r1, cs4r2, cs4r3, cs4r4, cs4r5, cs4r6, cs4r7, cs5, cs5r1, cs5r2, cs5r3, cs5r4, cs5r5, cs5r6, cs5r7, cs6, cs6r1, cs6r2, cs6r3, cs6r4, cs6r5, q11, q12r1, q12r2, q12r3, q12r4, q12r5, q12r6, q13r1, q13r2, q13r3, q13r4, q13r5, q13r6, q14r1, q14r2, q14r3, q14r4, q14r5, q14r6, q15r1, q15r2, q15r3, q15r4, q15r5, q15r6, q16r1, q16r2, q16r3, q16r4, q16r5, q16r6, q17, q17newr1, q17newr2, q17newr3, q17newr4, q17newr5, q17newr6, q17newr7, q18newr1, q18newr2, q18newr3, q18newr4, q18newr5, q18newr6, q18new99, q19newr1, q19newr2, q19newr3, q19newr4, q19newr5, q19newr6, q19newr7, q19newr8, q19newr9, q19newr99, q19r1, q19r2, q19r3, q19r4, q19r5, q19r6, q19r99, q20, q21, q22, q23, q24r1, q24r2, q24r3, q24r4, q24r5, q24r6, q25, q28r1, q28r2, q28r3, q28r4, q28r5, q2901r1, q2901r2, q2901r3, q2901r4, q2901r5, q2901r6, q2901r7, q2901r8, q2901r9, q2901r10, q2901r11, q2901r12, q2901r13, q2901r14, q2901r97, q29a01, q3001r1, q3001r2, q3001r3, q3001r4, q3001r5, q3001r6, q3001r7, q3001r8, q3001r9, q3001r10, q3001r11, q3001r97, q30a01, q3101r1, q3101r2, q3101r3, q3101r4, q3101r5, q3101r6, q3201r1, q3201r2, q3201r3, q3201r4, q3201r5, q2902r1, q2902r2, q2902r3, q2902r4, q2902r5, q2902r6, q2902r7, q2902r8, q2902r9, q2902r10, q2902r11, q2902r12, q2902r13, q2902r14, q2902r97, q29a02, q3002r1, q3002r2, q3002r3, q3002r4, q3002r5, q3002r6, q3002r7, q3002r8, q3002r9, q3002r10, q3002r11, q3002r97, q30a02, q3102r1, q3102r2, q3102r3, q3102r4, q3102r5, q3102r6, q3202r1, q3202r2, q3202r3, q3202r4, q3202r5, q2903r1, q2903r2, q2903r3, q2903r4, q2903r5, q2903r6, q2903r7, q2903r8, q2903r9, q2903r10, q2903r11, q2903r12, q2903r13, q2903r14, q2903r97, q29a03, q3003r1, q3003r2, q3003r3, q3003r4, q3003r5, q3003r6, q3003r7, q3003r8, q3003r9, q3003r10, q3003r11, q3003r97, q30a03, q3103r1, q3103r2, q3103r3, q3103r4, q3103r5, q3103r6, q3203r1, q3203r2, q3203r3, q3203r4, q3203r5, q2904r1, q2904r2, q2904r3, q2904r4, q2904r5, q2904r6, q2904r7, q2904r8, q2904r9, q2904r10, q2904r11, q2904r12, q2904r13, q2904r14, q2904r97, q29a04, q3004r1, q3004r2, q3004r3, q3004r4, q3004r5, q3004r6, q3004r7, q3004r8, q3004r9, q3004r10, q3004r11, q3004r97, q30a04, q3104r1, q3104r2, q3104r3, q3104r4, q3104r5, q3104r6, q3204r1, q3204r2, q3204r3, q3204r4, q3204r5, q2905r1, q2905r2, q2905r3, q2905r4, q2905r5, q2905r6, q2905r7, q2905r8, q2905r9, q2905r10, q2905r11, q2905r12, q2905r13, q2905r14, q2905r97, q29a05, q3005r1, q3005r2, q3005r3, q3005r4, q3005r5, q3005r6, q3005r7, q3005r8, q3005r9, q3005r10, q3005r11, q3005r97, q30a05, q3105r1, q3105r2, q3105r3, q3105r4, q3105r5, q3105r6, q3205r1, q3205r2, q3205r3, q3205r4, q3205r5, q33r1, q33r2, q33r3, q33r4, q33r5, q33r99, q34r1, q34r2, q34r3, q34r4, q34r5, q35, q36, q37r1, q37r2, q37r3, q37r4, q37r5, q37r6, q38r1, q38r2, q38r3, q38r4, q38r5, q38r6, q39r1, q39r2, q39r3, q39r4, q39r5, q39r6, q40r1, q40r2, q40r3, q40r4, q40r5, q40r6, q41r1, q41r2, q41r3, q41r4, q41r5, q41r6, from Table 2
UNION ALL 
select vertical,opportunity_name, project_name_, campaign_id, agency, account_name, sub_brand, record, uuid, date, status, q1r1, q1r2, q1r3, q1r4, q1r5, q1r6, q1r7, q1r99, q2, q3, q4, q5, q6a, q6b, q7a, q7b, q8a, q8b, q9, q10, cs1, cs1r1, cs1r2, cs1r3, cs1r4, cs1r5, cs1r6, cs1r7, cs1r8, cs1r9, cs1r10, cs1r11, cs1r12, cs1r13, cs2, cs2r1, cs2r2, cs2r3, cs2r4, cs2r5, cs2r6, cs2r7, cs2r8, cs2r9, cs2r10, cs3, cs3r1, cs3r2, cs3r3, cs3r4, cs3r5, cs3r6, cs3r7, cs3r8, cs3r9, cs3r10, cs4, cs4r1, cs4r2, cs4r3, cs4r4, cs4r5, cs4r6, cs4r7, cs5, cs5r1, cs5r2, cs5r3, cs5r4, cs5r5, cs5r6, cs5r7, cs6, cs6r1, cs6r2, cs6r3, cs6r4, cs6r5, q11, q12r1, q12r2, q12r3, q12r4, q12r5, q12r6, q13r1, q13r2, q13r3, q13r4, q13r5, q13r6, q14r1, q14r2, q14r3, q14r4, q14r5, q14r6, q15r1, q15r2, q15r3, q15r4, q15r5, q15r6, q16r1, q16r2, q16r3, q16r4, q16r5, q16r6, q17, q17newr1, q17newr2, q17newr3, q17newr4, q17newr5, q17newr6, q17newr7, q18newr1, q18newr2, q18newr3, q18newr4, q18newr5, q18newr6, q18new99, q19newr1, q19newr2, q19newr3, q19newr4, q19newr5, q19newr6, q19newr7, q19newr8, q19newr9, q19newr99, q19r1, q19r2, q19r3, q19r4, q19r5, q19r6, q19r99, q20, q21, q22, q23, q24r1, q24r2, q24r3, q24r4, q24r5, q24r6, q25, q28r1, q28r2, q28r3, q28r4, q28r5, q2901r1, q2901r2, q2901r3, q2901r4, q2901r5, q2901r6, q2901r7, q2901r8, q2901r9, q2901r10, q2901r11, q2901r12, q2901r13, q2901r14, q2901r97, q29a01, q3001r1, q3001r2, q3001r3, q3001r4, q3001r5, q3001r6, q3001r7, q3001r8, q3001r9, q3001r10, q3001r11, q3001r97, q30a01, q3101r1, q3101r2, q3101r3, q3101r4, q3101r5, q3101r6, q3201r1, q3201r2, q3201r3, q3201r4, q3201r5, q2902r1, q2902r2, q2902r3, q2902r4, q2902r5, q2902r6, q2902r7, q2902r8, q2902r9, q2902r10, q2902r11, q2902r12, q2902r13, q2902r14, q2902r97, q29a02, q3002r1, q3002r2, q3002r3, q3002r4, q3002r5, q3002r6, q3002r7, q3002r8, q3002r9, q3002r10, q3002r11, q3002r97, q30a02, q3102r1, q3102r2, q3102r3, q3102r4, q3102r5, q3102r6, q3202r1, q3202r2, q3202r3, q3202r4, q3202r5, q2903r1, q2903r2, q2903r3, q2903r4, q2903r5, q2903r6, q2903r7, q2903r8, q2903r9, q2903r10, q2903r11, q2903r12, q2903r13, q2903r14, q2903r97, q29a03, q3003r1, q3003r2, q3003r3, q3003r4, q3003r5, q3003r6, q3003r7, q3003r8, q3003r9, q3003r10, q3003r11, q3003r97, q30a03, q3103r1, q3103r2, q3103r3, q3103r4, q3103r5, q3103r6, q3203r1, q3203r2, q3203r3, q3203r4, q3203r5, q2904r1, q2904r2, q2904r3, q2904r4, q2904r5, q2904r6, q2904r7, q2904r8, q2904r9, q2904r10, q2904r11, q2904r12, q2904r13, q2904r14, q2904r97, q29a04, q3004r1, q3004r2, q3004r3, q3004r4, q3004r5, q3004r6, q3004r7, q3004r8, q3004r9, q3004r10, q3004r11, q3004r97, q30a04, q3104r1, q3104r2, q3104r3, q3104r4, q3104r5, q3104r6, q3204r1, q3204r2, q3204r3, q3204r4, q3204r5, q2905r1, q2905r2, q2905r3, q2905r4, q2905r5, q2905r6, q2905r7, q2905r8, q2905r9, q2905r10, q2905r11, q2905r12, q2905r13, q2905r14, q2905r97, q29a05, q3005r1, q3005r2, q3005r3, q3005r4, q3005r5, q3005r6, q3005r7, q3005r8, q3005r9, q3005r10, q3005r11, q3005r97, q30a05, q3105r1, q3105r2, q3105r3, q3105r4, q3105r5, q3105r6, q3205r1, q3205r2, q3205r3, q3205r4, q3205r5, q33r1, q33r2, q33r3, q33r4, q33r5, q33r99, q34r1, q34r2, q34r3, q34r4, q34r5, q35, q36, q37r1, q37r2, q37r3, q37r4, q37r5, q37r6, q38r1, q38r2, q38r3, q38r4, q38r5, q38r6, q39r1, q39r2, q39r3, q39r4, q39r5, q39r6, q40r1, q40r2, q40r3, q40r4, q40r5, q40r6, q41r1, q41r2, q41r3, q41r4, q41r5, q41r6, from Table 3

i’m getting the error “Unrecognized name: q6b; Did you mean q6a? at [1:194]”

I suspect where i went wrong is that i selected column names (ex: g6b) that are not in the table.

Instead of getting the column names for each 30+ of my datasets and using those in the select query, can I just use some type of null/0 value for the tables that don’t have the columns?

For example, if Table 1 has q1, q3, q6b and table 2 has q1, q2, q3, q6a, can i do this as my query

SELECT q1, 0, q3, 0, q6b from Table 1
UNION
Select q1, 2, q3, q6a, 0 from Table 2

I'm quite new to sql & never done unions before, so any help is appreciated!

Leave a Comment