Merge two or more tables in phpMyAdmin

Today, we’ll discuss how to merge two or more tables in phpMyAdmin. Sometimes we may need to merge multiple tables that have the same structure. So here, we explain to you a simple way to merge multiple tables in phpMyAdmin.

Checkout more articles on PHP

Example

Let's take an example where we will have three tables named `customers1`, `customers2` and `customers3`. We will merge all those tables and create a final table named `customers`.

For demo purposes, we are taking 3 records in each table but it helps to merge large records of the multiple tables as well.

Tables - Clue Mediator

All three tables contain the sample data as shown below in the images.

customers1 - Clue Mediator

customers2 - Clue Mediator

customers3 - Clue Mediator

Steps to merge two or more tables

Create a final table structure
Copy records from sub tables to final table
Update primary key if exist

1. Create a final table structure

First of all, we have to create a final table where we can merge all the sub table records. So follow one of the methods to create a final table structure.

Method 1:

Go to table customers1 > Operations > Copy table to (database.table)

Now write a final table name as `customers` and select the “Structure only” option then click on the “Go” button. Your selection should look like below.

Copy table to - Clue Mediator

Method 2:

In this alternative method, you can create a table with structure by executing the SQL query. Run the following SQL query.

create table customers like customers1;

When you click on the Go button or execute your query then the table named `customers` will be created.

2. Copy records from sub tables to final table

Now, you need to run the below query in phpMyAdmin to copy records from the `customers1` to `customers` table.

INSERT INTO customers SELECT * FROM customers1;

The same way, we will run the following queries to copy the records of the tables `customers2` and `customers3`.

/* Copy records from `customers2` to `customers` table */
INSERT INTO customers SELECT * FROM customers2;

/* Copy records from `customers3` to `customers` table */
INSERT INTO customers SELECT * FROM customers3;

That’s it to merge multiple tables.

3. Update primary key if exist

If we have used the primary key in tables and when we run the above queries then it will throw an error due to duplicate value for the primary key as shown in the below image.

Duplicate entry for primary key - Clue Mediator

To avoid this error, we need to do one more step before inserting the data.

First, we have to check the last value of the field which has assigned the primary key. After copying the table `customers1`, we will get the last value `3` of the `customer_id` field from the `customers` table.

Now, we have to update the primary key values of the `customers2` table. Run the following query.

UPDATE customers2 SET `customer_id` = 3 + `customer_id`;

After running the above query you can see the `customers2` table which has reordered the `customer_id` field starting from 4 as below image.

Updated customers2 - Clue Mediator

Now we can easily merge the `customers2` table into the `customers` table by running the following query.

INSERT INTO customers SELECT * FROM customers2;

The same way, we’ll run the following queries to update and merge the `customers3` table.

UPDATE customers3 SET `customer_id` = 6 + `customer_id`;

INSERT INTO customers SELECT * FROM customers3;

Finally, we will have all records in table `customers` as shown in the below image.

customers - Clue Mediator

That’s it for today.
Thank you for reading. Happy Coding..!!