Quick Reach
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:
- tbl_employee
- 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:
1
2
3
|
insert into tbl_customers
select emp_id, emp_name, emp_salary from tbl_employee
|
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.
1
2
3
|
insert into tbl_customers
select emp_id, emp_name, emp_salary from tbl_employee where emp_age <= 30
|
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.
1
2
3
4
5
6
7
|
INSERT INTO test_db2.tbl_customers
SELECT emp_id, emp_name, emp_salary
FROM test_db.tbl_employee
WHERE emp_age <=30
|
Also see SQL Insert into | Select statement of SQL | The SQL Where clause
Leave A Comment?