Tutorial Gateway

MySQL Insert Statement

by

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);

OUTPUT

MySQL Insert Statement 1

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.

MySQL Insert Statement 2

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.

OUTPUT

MySQL Insert Statement 3

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);

OUTPUT

MySQL Insert Statement 4

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;

OUTPUT

MySQL Insert Statement 5

Let us see the output

MySQL Insert Statement 6

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);

OUTPUT

MySQL Insert Statement 7

Let us the records present in the customer table

MySQL Insert Statement 8

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

MySQL Insert Statement 9

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

OUTPUT

MySQL Insert Statement 10

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';

OUTPUT

MySQL Insert Statement 11

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

OUTPUT

MySQL Insert Statement 12

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

MySQL Insert Statement 13

CODE

INSERT INTO salesdetails (Id, Name, Country)
VALUES (1, 'Tutorial', 'USA');

OUTPUT

MySQL Insert Statement 14