Sometimes you may need to copy 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 copy 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.
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 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 |
+------+------------+-----------+
Ubiq makes it easy to visualize data, and monitor them in real-time dashboards. Try Ubiq for free.
