Menu

▼

Saturday, November 13, 2021

how to create insert select query in MySQL


Let me show various use cases of the insert query. In this article, I am going to cover the following:

  1. Simple INSERT statement to add data to the table
  2. Use INSERT Statement to add multiple rows in the table
  3. INSERT INTO SELECT clause to insert the output generated by the SELECT query
  4. INSERT IGNORE clause to ignore the error generated during the execution of the query

Simple INSERT query example

Suppose I want to insert the first name and last name of the student. Run the following query:

INSERT INTO tblstudent (student_first_name, student_last_name) values ('Get','Solution');


Insert multiple rows in the table

We want to insert details of the two students in the tblstudent table. To do that, execute the following query:

INSERT INTO tblstudent (student_first_name, student_last_name, student_joining_date) values ('Get','Solution',current_date()),('Explore','it',current_date());


INSERT INTO SELECT Query

Suppose we want to insert the result-set that is generated by another SELECT query in the table, we can use INSERT INTO SELECT Query. The syntax is the following:

INSERT INTO destination_table_name(column_1, column_2)
SELECT 
   column_1,column_2
FROM 
   source_table
WHERE
   condition;
   
   
As you can see in the syntax, instead of using the VALUES clause, we have used the SELECT query. Here SELECT statement retrieves the data from another table or by joining different tables. This query is very helpful when you want to create a backup of a specific table.


Let’s look at the query of the INSERT INTO MySQL command:

$sql = "INSERT INTO InsertTbl (area, city, product_desc, product_price)
       SELECT `area`, OldTable.`city`,`product_desc`,`product_price`
                  FROM OldTable INNER JOIN ReferenceTb1
                    ON OldTable.city = RefrneceTb1.city
                        ORDER BY area ASC, city ASC" ;
at