How to Insert With Select in MySQL

1. INSERT in MySQL
2. SELECT in MySQL
3. INSERT INTO SELECT in MySQL

MySQL is an open-source database that is freely available and used in large and small applications. Its use case can get found in school-college applications, enterprise applications, e-commerce platforms, and many more.

Relational Databases are easy to understand and interpret. Hence, the database seeks attention from a wide variety of folks.

The database is created, maintained, and managed by the MySQL team. It holds the ownership of its releases and brings new features.

CRUD operations like create, read, update, and delete are the basic operations needed by the program each time with every DB to perform some manipulations. The language used to query the MySQL relational database is called a query language.

The query language is a rich and predefined protocol that helps insert and manipulate data.

INSERT in MySQL

The syntax to insert data in Database in MySQL is as below.

INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);

Let’s understand the insert syntax below.

• INSERT INTO keyword gets its place at the initial first position, intimating MySQL database that inserts operation gets performed.
• Next is the table_name that is the table’s name that specifies in which table the data needs to get inserted.
• The column names are the optional content in braces stating the column names are optional entities. There is a privilege to insert values without providing column names. But in that case, all the values to the existing columns must be given in the desired sequence.
• Values keywords specify that the next section will be the set of values inserted in the column. These are again optional, based on the number of columns. If all values are to provide, the sequence of the columns should get maintained along with the braces.
• ; is the terminal operator used to specify that the query has ended.
• Example: INSERT INTO student (id,name,age) values(1,"John","18");

SELECT in MySQL

The syntax to select data in Database in MySQL is as below. Select statement gets used to select the specific lines and data from the table in the MySQL database.

SELECT column1, column2, ...FROM table_name where condition;

Let’s understand the select syntax.

• Select keyword specifies and intimates the DB that the query triggered to DB is a select statement.
• Column1 and Column2 specify the data that needs to be retrieved from the table.
• FROM keyword points to the table that needs to get retrieved from the database.
• table_name gives the exact name of the table from which one needs retrieval.
• where condition provides a condition in which selection is made based on some criteria.
• Example1: Select * from student; The statement will select all the rows of the student table.
• Example2: Select id, name, age from the student; The statement will select all the rows of id, name, and age from the student table.

INSERT INTO SELECT in MySQL

Now Insert Into Select statement copies data from a selected table and inserts the data in another table. The query that copies selected data in another table is:

INSERT INTO table2 SELECT * FROM table1 WHERE condition;

The prerequisites and a must condition to insert data in another table is the column data type must be the same for the tables.

Let’s understand the insert into select query in detail.

• Insert Into statement intimates MySQL database that insertion will happen after this keyword in the query statement.
• Table2 is the table name wherein the data needs to get copied or the destination table.
• Select keyword will act as a subquery that says the selection will happen on the source table.
• * or column specifies which column name is to get selected.
• FROM table1 is the source table where the copy of data is required.
• The WHERE condition is the extra element to get specified data selection based on given criteria. It works as a filter that provides a filter on specified columns instead of all the columns getting selected.
• Examples: Insert into student_copy select * from student where age=28; The statement will copy the rows of student table in student_copy where age is 28.