SQL insert select

This is possible to copy the table data from one table to another by using SQL Insert into with select statement.

The insert into statement specifies the table where data needs to be copied. Whereas the select specifies where data should be copied from.

You can copy whole data from one table to another or choose specific column(s) to be copied by using the insert with select. Also, you can use the Where clause with a condition to copy only specific data or limited rows from one table to other.

General syntax of using SQL Insert into with select

The general syntax of copying data from one table to another is:

Copying whole table data by using insert and select

Insert into table_copy_to

Select * from table_copy_from

To copy whole data with Where clause

Insert into table_copy_to

Select * from table_copy_from where column_name = value

To copy specific columns data

Insert into table_copy_to

(column1, column2, column3 ….columnx)

Select column1, column2, column3….columnx from table_copy_from

This can be useful for many scenarios, for example, employees of one company may become customers of an insurance company. In that case, this will make the task quite easier if you properly use insert and select combination with two different databases at two different locations.

Insert into with select example

This example shows how to copy whole data from one table to another by using the SQL insert select combination. For that suppose our database contains two tables:

1. tbl_employee
2. tbl_customers

The scenario is that the employees of a company become customers of an insurance company, just for illustration purpose. We need to copy emp_id, emp_name and emp_salary into tbl_customers table (first into the same database)

The SQL query will be:

SQL insert into select example for selected columns

This example copies data of only those employees with the age less than or equal to 30 by using the SQL insert into select with where clause.

select insert example of copying from one database to other

The select insert query combination becomes even more powerful if used to copy data from one database to another or from one server to other. Also in scenarios where you want to copy data from one company’s database to the other. In that case, your tables are lying in different databases and in different locations.

The example shows how to copy data from tbl_employee’s table of test_db database to tbl_customer of test_db2 database by using the select and insert into statements.

Also see SQL Insert into | Select statement of SQL | The SQL Where clause