How to Copy Data From One Table to Another in SQL

Last updated on September 11th, 2024 at 05:57 am

Sometimes you may need to clone data from one table to another in your database. In this article, we will look at how to copy data from one table to another using SQL. It is a very convenient way to transfer data from one table to another. You can use these steps to create duplicate data from one table to another in MySQL, PostgreSQL, SQL Server and Oracle.

How to Copy Data From One Table to Another table in SQL

Here are the steps to copy data from one table to another table.

Let’s say you have the following table employees(id, first_name, last_name)

mysql> create table employees(id int, 
      first_name varchar(255),
      last_name varchar(255));

mysql> insert into employees(id, first_name, last_name)
       values(1,'John','Doe'),
       (2,'Jane','Doe');

mysql> select * from employees;
+------+------------+-----------+
| id   | first_name | last_name |
+------+------------+-----------+
|    1 | John       | Doe       |
|    2 | Jane       | Doe       |
+------+------------+-----------+

Also read : How to Update a Column Based on Another Column in SQL

Let us say you want to copy data from employees table to another table employees2(id, first_name, last_name).

mysql> create table employees2(id int,
       first_name varchar(255),
       last_name varchar(255));

Here is the SQL query syntax to copy data from one table to another using INSERT INTO statement.

INSERT INTO table1 (column1, column2, ...)
select column1, column2, ...
from table2

In the above query, we select column1, column2, … from table2 and insert them into table1. It allows you to insert multiple rows in table.

Also Read : How to Update Multiple Columns in MySQL

Please note, the columns used in the INSERT INTO statement and SELECT statement must have same name, data type and order. Otherwise, you will get an error.

Here is the SQL query to copy data from employees table to employees2 table.

mysql> insert into employees2(id, first_name, last_name)
       select id, first_name, last_name
       from employees;

mysql> select * from employees2;
+------+------------+-----------+
| id   | first_name | last_name |
+------+------------+-----------+
|    1 | John       | Doe       |
|    2 | Jane       | Doe       |
+------+------------+-----------+

Copy Some Rows to Another Table

The above example will copy all rows in source table to the destination table. If you want to copy only some of the rows from source table to destination table, you can use a WHERE clause in your SELECT statement as shown below. Here is an example to copy only row where first_name=’Jane’.

mysql> insert into employees2(id, first_name, last_name)
       select id, first_name, last_name
       from employees
       where first_name='Jane';

mysql> select * from employees2;
+------+------------+-----------+
| id   | first_name | last_name |
+------+------------+-----------+
|    2 | Jane       | Doe       |
+------+------------+-----------+

Copy Data to Table With Same Schema

If both the source and destination tables have same schema, then you can directly use this simple query instead of listing all column names of both tables as shown below.

mysql> insert into employees2 
       select * from employees

Copy Data Across Databases

All the above examples assume that the source and destination tables are in the same database. But if they are in different tables, then you can still copy them using INSERT INTO…SELECT statement by simply mentioning the database name before table name as shown below.

Let us say you want to duplicate all data from employees table located in db1 to employees2 table in db2 then here is the query to do so.

mysql> insert into db2.employees2(id, first_name, last_name)
       select id, first_name, last_name
       from db1.employees;

mysql> select * from employees2;
+------+------------+-----------+
| id   | first_name | last_name |
+------+------------+-----------+
|    1 | John       | Doe       |
|    2 | Jane       | Doe       |
+------+------------+-----------+

You can also use MySQLdump to copy specific tables in a database.

Conclusion

In this article, we have learnt how to copy data from one table to another in SQL. You can use this statement in all major databases such as MySQL, MariaDB, PostgreSQL, SQL Server and Oracle. It is very useful in quickly copying data across tables in same database.

Ubiq makes it easy to visualize data, and monitor them in real-time dashboards. Try Ubiq for free.