The MySQL Insert statement is used to insert, or add new records into MySQL table. In this article we will show you, How to write Insert Query in MySQL with example.
To demonstrate the Insert Query in MySQL, we will use the tables that we created in our previous post. Before we get into the practical example, let us the syntax behind this MySQL Insert.
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 will be 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 destination table then we can simply write as:
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 new record into 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 incremental column, and it will be updated automatically.
As we said above, If you are inserting data for all the existing columns then ignore the column names (Syntax 2). It means above statement can also be written as:
USE company; INSERT INTO customers VALUES (2, 'MySQL', 'Server', 'Database', 'Programming', 95000, 1500.52);
TIP: It is not good practice to ignore the column names in insert statement. So, always provide the column names.
Above queries will 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 defined by the destination table structure.
MySQL Insert Multiple Rows
In this example, We are going to insert multiple rows into customer Table in 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 are going use UNION ALL (or UNION) to insert multiple record into 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
Following MySQL Insert query is the most popular way to insert multiple record into 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
MySQL Insert few columns example
It is not mandatory to insert all the column values at one go. You can omit few column while inserting, and update them later. In this MySQL insert statement example, We are going to insert few column values into company table.
USE company; INSERT INTO customers (First_Name, Last_Name, Education, Sales) VALUES ('Jacob', 'Johnson', 'Degree', 4500.25);
Above Insert query will insert data into First_Name, Last name, Education, and Sales columns. NULL values will be inserted for the remaining columns
TIP: When you are inserting few records into the table, you must specify the column names.
MySQL Insert Into Example
Instead of hard coding all the values, you can use INSERT INTO SELECT Statement to insert records from one table to another. In this example, We are going to select rows from the country table, and insert them into 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 to refer Insert Into Select Statement article. Let us see whether the query inserted the selected data into the destination table or not
MySQL Insert Example – Command Prompt
In this example, We are going to insert one record into sales details table using command prompt. Before we get into the query, the data inside our table is as shown below
INSERT INTO salesdetails (Id, Name, Country) VALUES (1, 'Tutorial', 'USA');