insert into with select statement of MySQL

In some scenarios, you will need to copy table data of one table to another. You may need to copy whole data, a few columns data of a table or only the subset of data or fewer rows with specific columns from one table to another.

The insert into – select statement combination allows us to do that. As such, the insert into statement is used to enter data, so we will specify the table name where data needs to be entered there. Whereas in the select statement the table from where you need to copy data is specified.

By using a simple select statement, you can insert whole data from one table to another (specified in the insert into). To get fewer rows, you can use the Where clause with the select statement. Similarly, you can use column names in the select statement to copy only specific column data.

Following is the syntax to use insert into – select combination followed by examples in MySQL.

Syntax of MySQL Insert into with select

Following is the syntax to use MySQL select – insert:

Copying whole table data

Insert into table_copy_to

Select * from table_copy_from

insert into – select MySQL to Copy data with Where clause

Insert into table_copy_to

Select * from table_copy_from where column_name = value

Syntax to copy specific columns data

Insert into table_copy_to

(column1, column2, column3 ….columnx)

Select column1, column2, column3….columnx from table_copy_from

An example insert – select MySQL

Following is an example to explain how you can copy data. Suppose we have two tables:

1. tbl_employee
2. tbl_customers

We will copy emp_id, emp_name and emp_salary from tbl_employee table into tbl_customers table.

The MySQL query will be:

insert into and select with where clause

The following example uses the where clause to copy only a few rows with the given condition:

Also see: Insert into