Insert into select query in sql

Blog Bustle 07/03/2020

Insert into select query is used to insert record into table from another table. It is fast process to copy data from one table and insert into another table.

Points to remember:

1. Remember that target table column's data type should be match with source table column's data type.

2. Target table existing records will be the same as before after running insert into select query.

3. First select database before running any query related to that database otherwise you will get 'no database elected' error.

insert into select query syntax

1. Copy all table data and insert into another table

insert into table_1
select * from table_2 where condition;

in this insert into select query all data from table_2 will get copied and inserted into table_1 according to where condition. Here where condition is optional.

2. Copy some column data from table and insert into another table

insert into table_1(column_1, column_2, column_3, ....)
select column_1, column_2, column_3, .... from table_2 where condition;

in this insert into select query some column data from table_2 will get copied and inserted into table_1 according to where condition. Here where condition is optional.

For example, we have two tables named student1 and student2 in database.

MariaDB [student]> select * from student1;

MariaDB [student]> select * from student2;

insert all student2 data into student1 and query will be

insert into student1 select * from student2;

after running this insert into select query, output will be

MariaDB [student]> select * from student1;

if we want to copy only email, mobile from student2 where id is 3 and insert into student1, query would be

insert into student1(email, mobile) select email, mobile from student2 where id = 3;

after running this insert into select query, output would be

MariaDB [student]> select * from student1;

So insert into select query is easy to use. Hope you like this tutorial.

Other important links

No Database Selected error

Alter query in sql

Create table sql query

Delete table sql query

Insert into select query in sql searches:

Tags: #mysql