How to write Insert Query in MySQL with example?. The MySQL Insert statement is to insert or add new records into a MySQL table. To demonstrate the Insert Query in MySQL, we use the tables that we created in our previous post.
MySQL Insert Syntax
The basic syntax of Insert statement in MySQL is as shown below:
INSERT INTO Destination Table (Column1, Column2,..., ColumnN) VALUES (Column1_Value, Column2_Value,..., ColumnN_Value)
- Destination Table: Provide fully qualifies Table name in which you want to insert records
- Column1…ColumnN: It allows us to choose the number of columns from the table. It may be One or more.
- Column1_Value…ColumnN_Value: Please specify the values that you want to insert. For instance, Column1_Value inserted in Column1. If you omit the field names, you must specify the values (i.e., filed values) in the order defined by the destination table structure
If you are inserting values for all the column in the destination table, then write
INSERT INTO Destination Table VALUES (Column1_Value, Column2_Value,..., ColumnN_Value)
MySQL INSERT Workbench Example
In this MySQL Insert Statement example, we are going to insert a new record into the customers table.
USE company; INSERT INTO customers (First_Name, Last_Name, Education, Profession, Yearly_Income, Sales) VALUES ('Tutorial', 'Gateway', 'Masters', 'Admin', 120000, 14500.25);
NOTE: Here, we haven’t inserted the CustID value. Because it is an auto-increment column, and it updates automatically. Please refer to Create Table article.
As we said above, If you are inserting data for all the existing columns, then ignore the column names (Syntax 2). It means the above MySQL statement can also write as:
USE company; INSERT INTO customers VALUES (2, 'MySQL', 'Server', 'Database', 'Programming', 95000, 1500.52);
TIP: It is not a good habit to ignore the column names in the insert statement. So, always provide the column names.
The above queries insert data into First_Name, Last_Name, Education, Occupation, Yearly_Income, and Sales columns. Whenever you omit the field names, you must specify the column_values in the order determined by the destination table structure.
MySQL Insert Multiple Rows
In this example, we insert multiple rows into the customer Table in a more traditional way.
USE company; INSERT INTO customers VALUES (3, 'James', 'Smith', 'Degree', 'Developer', 85000, 38313.56); INSERT INTO customers VALUES (4, 'Michael', 'Smith', 'BTech', 'Developer', 80000, 34810.62);
Here, we use UNION ALL (or UNION) to insert multiple records into the company table.
-- Insert Query in MySQL USE company; INSERT INTO customers SELECT 5, 'Maria', 'Garcia', 'Hig School', 'Developer', 55000, 32013.12 UNION ALL SELECT 6, 'Maria', 'Rodriguez', 'Post Graduate', 'Senior Developer', 980000, 30510.62;
Let us see the output
The following MySQL Insert query is the most popular way to insert multiple records into a MySQL table.
-- Insert Query in MySQL USE company; INSERT INTO customers (First_Name, Last_Name, Education, Profession, Yearly_Income, Sales) VALUES ('Robert', 'Smith', 'High School', 'Software Developer', 70000, 34013.12), ('James', 'Johnson', 'Under Graduate', 'Asst Programmer', 59000, 20510.62), ('John', 'Smith', 'B Tech', 'programmer', 78000, 18500.25);
Let us the records present in the customer table
Insert few columns example
It is not mandatory to insert all the column values at one go. You can omit a few columns while inserting and update them later. In this MySQL insert statement example, we insert a few column values into the company table.
USE company; INSERT INTO customers (First_Name, Last_Name, Education, Sales) VALUES ('Jacob', 'Johnson', 'Degree', 4500.25);
The above Insert query inserts data into First_Name, Last name, Education, and Sales columns. NULL values inserted for the remaining columns
TIP: When you are inserting a few records into the table, you must define the column names.
MySQL Insert Into Example
Instead of hard coding all the values, you can use INSERT INTO SELECT Statement to insert rows from one table to another. In this example, we select rows from the country table and insert them into the countrydetails table. Here, we are going to restrict the rows using the WHERE Clause.
INSERT INTO company.countrydetails (Code, Name, Continent, Region, SurfaceArea, IndepYear, Population, LocalName, Capital) SELECT Code, Name, Continent, Region, SurfaceArea, IndepYear, Population, LocalName, Capital FROM world.country WHERE Continent = 'Asia' AND Region = 'Southern and Central Asia';
We are not going to explain the code implementation at this point. I suggest you refer Insert Into Select Statement article. Let us see whether the query inserted the selected data into the destination table or not.
MySQL Insert Command Prompt Example
In this example, we insert one record into the sales details table using the command prompt. Before we get into the query, the data inside our table is
INSERT INTO CODE
INSERT INTO salesdetails (Id, Name, Country) VALUES (1, 'Tutorial', 'USA');