MySQL Insert Statement 7

MySQL Insert Statement

by

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);
MySQL Insert Statement 1

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.

MySQL Insert Statement 2

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 Statement 3

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);
MySQL Insert Statement 4

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;
MySQL Insert Statement 5

Let us see the output

MySQL Insert Statement 6

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);
MySQL Insert Statement 7

Let us the records present in the customer table

MySQL Insert Statement 8

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

MySQL Insert Statement 9

TIP: When you are inserting a few records into the table, you must define the column names.

MySQL Insert Statement 10

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';
MySQL Insert Statement 11

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 Statement 12

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

MySQL Insert Statement 13

INSERT INTO CODE

INSERT INTO salesdetails (Id, Name, Country)
VALUES (1, 'Tutorial', 'USA');
MySQL Insert Statement 14