Append Two, Three or Multiple Tables In Google BigQuery

Sometimes we need to append two, three or more tables or selected columns of multiple tables in bigquery. So in this post we will learn -

1. How to append more than one table in bigquery ?
2. How to append selected columns of multiple tables in bigquery ?
3. How to append or update data in previously available table in bigquery by uploading csv ?

Append More than One Table in BigQuery

With All Columns having same schema

If we want to append two tables with all columns of bigquery then we must have both tables with the same schema in bigquery. We can append all the tables by running a SQL QUERY (UNION ALL operator) and save results as csv or bigquery table.

Let’s understand with an example -

I have a dataset named employees_data_ , there are two tables in it,
First is employees_data_female

Employee_Name	EmpID	Gender	Marital_Status	Position
Maria Gonzalez	1988299991	Female	Separated	IT Support
Jessica Bunbury	1504073368	Female	Married	Area Sales Manager
Susan Ferguson	1502072511	Female	Married	Production Technician I

And second is employees_data_male -

Employee_Name	EmpID	Gender	Marital_Status	Position
James Cockel	1599991009	Male	Single	Production Technician I
Edward Buck	1504073313	Male	Married	Area Sales Manager
Michael Alber	1501072311	Male	Divorced	Production Manager

These tables have the same schema. Now we append these tables by running this query -

SELECT * FROM `trydata.employees_data.employees_data_female`
UNION ALL
SELECT * FROM `trydata.employees_data.employees_data_male`


This query will return a appended table with having data of both tables -
Appended table-

Employee_Name	EmpID	Gender	Marital_Status	Position
Edward Buck	1504073313	Male	Married	Area Sales Manager
James Cockel	1599991009	Male	Single	Production Technician I
Jessica Bunbury	1504073368	Female	Married	Area Sales Manager
Maria Gonzalez	1988299991	Female	Separated	IT Support
Michael Alber	1501072311	Male	Divorced	Production Manager
Susan Ferguson	1502072511	Female	Married	Production Technician I

We can save these results or explore data with in Google Data Studio.If you want to three or more tables just follow same process by adding UNION ALL with next table.

Append Selected Columns of tables

If we want only some selected columns of both tables then we can also do this. Let's assume we want only three columns - Employee_Name, Gender, Marital_Status. Now we run this SQL Query -

SELECT Employee_Name, Gender, Marital_Status FROM `trydata.employees_data.employees_data_female`
UNION ALL
SELECT Employee_Name, Gender, Marital_Status FROM `trydata.employees_data.employees_data_male`


This Query will return this table-

>

Employee_Name	Gender	Marital_Status
Edward Buck	Male	Married
James Cockel	Male	Single
Jessica Bunbury	Female	Married
Maria Gonzalez	Female	Separated
Michael Alber	Male	Divorced
Susan Ferguson	Female	Married

Append or Update Data in Previously Available Table by Uploading CSV

We can also append data to previously available tables in bigquery. For doing this click on JOB HISTORY and open the same job history of the table where we want to append data. After opening the job history click on Repeat load job , this will open the same dialog box that appears while loading data into bigquery.

Now browse the table with the same schema which you want to append. Scroll down and click on advance options and select Append to table in write preference dropdown.You can also enter header rows to skip. Now click on the crate table.This will append uploaded csv data to previously available data in table .