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.
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 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.
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;
id | name | mobile | |
---|---|---|---|
1 | karan | karan@gmail.com | 1234567890 |
2 | mohit | mohit@gmail.com | 1234567891 |
MariaDB [student]> select * from student2;
id | name | mobile | |
---|---|---|---|
3 | sachin | sachin@gmail.com | 1234567892 |
4 | ayush | ayush@gmail.com | 1234567893 |
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;
id | name | mobile | |
---|---|---|---|
1 | karan | karan@gmail.com | 1234567890 |
2 | mohit | mohit@gmail.com | 1234567891 |
3 | sachin | sachin@gmail.com | 1234567892 |
4 | ayush | ayush@gmail.com | 1234567893 |
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;
id | name | mobile | |
---|---|---|---|
1 | karan | karan@gmail.com | 1234567890 |
2 | mohit | mohit@gmail.com | 1234567891 |
3 | NULL | sachin@gmail.com | 1234567892 |
So insert into select query is easy to use. Hope you like this tutorial.
Sql Insert Query
Sql Update Query
Sql & Mysql difference
Sql Delete Query
Sql Select Query
Mysql no database selected error 1046
Sql Create Table
Sql Drop Table
Sql Alter Query(add, drop, modify & change column)
Sql Truncate Table
Sql Insert Into Query
Sql Rename Table
Sql DISTINCT Keyword
Sql WHERE Clause
Sql Like operator
Sql AND operator
Sql OR operator
Sql NOT operator
Combining Sql AND, OR & NOT operator
Sql IN operator
Sql NOT IN operator
Sql Count, Sum, Avg, Min, Max
Sql Order By clause
Sql Limit clause
Sql BETWEEN operator
Sql Group By clause
Sql Aliases
Sql Inner Join
Sql Left Join
Sql Right Join
Mysql Create Database
Mysql Create Database Command Line
Mysql Delete Database
Mysql Delete Database Command Line
Mysql Show Databases
Mysql Show Databases Command Line
Mysql Select Database
Mysql Select Database Command Line
Mysql Backup Database
Mysql Import Database
Mysql Copy Database
Mysql Rename Database
Truncate Database Mysql