MYSQL INSERT INTO

INSERT keyword in MYSQL is used to add the data to tables, specifically new rows. In this article, we will be talking about how to insert records in the tables.

Table of Contents

• MYSQL INSERT INTO TABLE
• INSERTING MULTIPLE ROWS IN MYSQL
• MYSQL INSERT INTO SPECIFIC COLUMNS

MYSQL INSERT INTO TABLE

This is the most common scenario when we want to add rows to a table.

Syntax:- 

INSERT INTO <table_name> (col1, col2, col3, ….) VALUES (value1, value2, value3……);

Here, <table_name> is the table’s name where you want to insert the data. col1,col2,col3…. is the list of columns whose values need to be inserted and values1,value2,value3… are the list of actual values.

Example:-

To have a better understanding of the concept, we will be:

• Creating a table students_college_A.
• Adding data to students_college_A using INSERT INTO.

students_college_A is designed to have columns student_no, first_name, last_name, stream_name. Here student_no will be incremented automatically and is the primary key of the table.

CREATE TABLE students_college_A (
    student_no INT AUTO_INCREMENT,
    first_name VARCHAR(255),
    last_name VARCHAR(255),
    stream_name VARCHAR(255),
    PRIMARY KEY (student_no)
);

Currently the table is empty. Let us verify this through a SELECT command.

SELECT * FROM students_college_A;

Output :-

We will now add some rows in this table using INSERT. We are trying to add five rows to table students_college_A; therefore, we are writing five INSERT statements, all separated by ‘;’.

 INSERT INTO students_college_A (first_name,last_name,stream_name) VALUES("Aditi","Verma","Commerce");
 INSERT INTO students_college_A (first_name,last_name,stream_name) VALUES("Mudita","Sharma","Arts");
 INSERT INTO students_college_A (first_name,last_name,stream_name) VALUES("Ritika","Joshi","Commerce");
 INSERT INTO students_college_A (first_name,last_name,stream_name) VALUES("Veronica","Ghosh","Commerce");
 INSERT INTO students_college_A (first_name,last_name,stream_name) VALUES("Supere","Basil","Commerce");

Since, student_no is auto increment we need not add it in the column and values list. Let us now see if the above data has been added to the table students_college_A.

SELECT * FROM students_college_A;

Output:-

So, the INSERT query did its job of adding the rows effectively.

PRO TIP: We can skip the column list in INSERT statement if we are adding data for all the columns in a row.

INSERTING MULTIPLE ROWS IN MYSQL

Although we added 5 rows in the above section as well but there we had to execute 5 separate statements. Now, let us see how to add multiple rows to a table by executing a single INSERT statement. Observe the minute differences in the syntax.

Syntax:-

INSERT INTO <table_name> (col1, col2, col3, ….) 
VALUES 
(value1, value2, value3……),
(value1, value2, value3……),
(value1, value2, value3……);

Again here, <table_name> is the table’s name in which you want to insert the data.  col1,col2,col3…. is the list of columns whose values are to be inserted, and values1,value2,value3… is the list of actual values. Each row is separated by ‘,’ and we end the statement by a ‘;’. 

Example:-

In this example we will work with an existing table students_college which is empty at the moment.

SELECT * FROM students_college;

Output:-

Now, we will add data for multiple rows simultaneously and check if the insert was successful by a SELECT query.

INSERT INTO students_college (student_no, first_name, last_name, stream_name) 
 VALUES (1,"Aditi","Verma","Commerce"),
 (2,"Sujata","Sharma","Science"),
 (3,"Rujata","Moining","Science"),
 (5,"Rammy","Goswal","Arts");

SELECT * FROM students_college;

Output :-

As we can see five rows added to table students_college.

MYSQL INSERT INTO SPECIFIC COLUMNS

At times we need to insert values only into a few columns instead of all in a table. Specifying only a few column names in the INSERT statement is possible; the data is added to only those columns in the row. Let us look into an example to clarify our understanding. We are using the same table students_college, which already has five rows of data. 

Now, we will add another row with only student_no and first_name columns.

 INSERT INTO students_college (student_no , first_name) VALUES (9,"Richa");
 SELECT * FROM students_college;

Output:-

We can see that another row is added with values in columns student_no and first_name while last_name and stream_name is null.

We hope this article helps you with basic concepts of INSERT INTO in MySQL.