INSERT INTO table | Inserting rows in a table in MySQL

INSERT INTO 

The INSERT INTO statement is used in MySQL to insert rows into a table. One or more rows can be inserted into a table using this statement.

Syntax:

INSERT INTO table_name (column1, column2, ….)
VALUES (value1, value2, ….);

The above syntax can be used for inserting rows into a table.

In the above syntax:

• table_name, written after the INSERT INTO statement shows the table in which the rows will be inserted,
• column names are written after the table_name in which you want to insert value in a new row.
• After the column names, VALUES are written in the order corresponding to the column names written above.

For example:

The above-written table named ‘students’ contains the details of students. 

INSERT students (roll_no, name, mobile)
VALUES ('3', 'Prashant', '6266484XXX');

The above query will insert a new row in the above table and the new table will look like below:

It can be seen that a new row with the given values in the query is inserted into the above table.

It is not necessary to insert values in all the columns all the time. We can insert values in some specified columns.

Inserting values in specified columns

INSERT INTO students (roll_no, name)
VALUES ('7', 'Ujjwal')

The above-written query has values written for only two columns, roll_no, and name. It will insert the default value in other columns which are not specified.

Now, the table will look like given below:

It can be seen in the above table, a new row is inserted in the given table and the default value is inserted in the mobile column which is null for that column.

Inserting multiple rows using INSERT INTO

The INSERT INTO statement can be used to insert multiple rows in a table.

Syntax:

INSERT INTO table_name (column1, column2, ….)
VALUES (value1, value2, ....),
       (value1, value2, ...),
       (value1, value2, ...),
       ....................;

The above syntax can be used to insert multiple rows into a table.

For example:

INSERT INTO students (roll_no, name, mobile)
VALUES ('3', 'Vivek', '8405819XXX'),
       ('4', 'Karan', '8783635XXX'),
       ('5', 'Manish', '8873732XXX');

The above query will insert 3 rows in the original given table students and the new table will now look like below:

Using SELECT statement instead of VALUES in INSERT INTO

We used values in the INSERT INTO statement for each column of a row that we wanted to insert. Now, the SELECT statement can also be used instead of values to insert a row into a table.

Syntax:

INSERT INTO table_name (colum1, column2, ….)
SELECT (column1, column2, ….)
FROM anothertable_name
WHERE condition;

For example:

The above-table named ‘customers’ contains the details of the customers.

INSERT INTO students (roll_no, name, mobile)
SELECT (Id, name, mobile)
FROM customers
WHERE Id<12;

The above query will select the rows from the “customers” table which fulfill the given condition and insert them into the original given“students” table. Now, the table will look like shown below:

The above-shown table is the new table after the insertion.

INSERT IGNORE statement in MySQL

While inserting one or more rows into a table if an error occurs during the process, MySQL terminates the statement and returns an error and no rows get inserted into the table. 

INSERT IGNORE statement is used in MySQL to avoid this situation. When we use INSERT IGNORE and an error occurs, MySQL ignores the rows with invalid data and inserts the rows with valid data.

Syntax:

INSERT IGNORE INTO table_name (column1, column2, ....)
VALUES (value1, value2, ....),
       (value1, value2, ....),
	   .....................;

For example:

INSERT IGNORE INTO students (roll_no, name, mobile)
VALUES ('2', 'Abhinav', '974523XXX'),
	   ('3', 'Amit', '823456XXX');

The new table after the execution of the above query will look like shown below:

In the above table, we can see that only one row is inserted and another is ignored. This is because roll_no is a primary key here, and roll_no having value 2 is already present in the table and the Primary key can't be duplicated. Hence, it is ignored and another row is inserted.