Insert in MySQL

Introduction to Insert in MySQL

The main goal of a database system is to store data into tables. To define the functionality in the database, we have different kinds of SQL commands. In this topic, we are going to learn about Insert in MySQL.

SQL commands can be categorized into the following:

• DDL (Data definition language)
• DML (Data manipulation language)
• DQL (Data query language)
• DCL (Data control language)
• TCL (Transaction control language)

Here in this article, we are going to focus mainly on DML. As we can see, the name Data Manipulation language, so once the table/database is created, we require DML commands to manipulate something. Merits of using these commands are if in case any wrong changes happened, we can roll back/undo it.

Following are the commands in DML:

1. INSERT: Used to insert new rows into the table.

INSERT into employee
Values(101,’abcd’);

2. DELETE: Used to delete the single row or entire records in a table.

DELETE TABLE employee;

3. UPDATE: Used to update existing records in a table.

UPDATE employee
SET col1 = new_col
WHERE condition;

Syntax of Insert Command in MySQL

We can write the INSERT INTO statement in the following two ways.

Way #1

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

Way #2

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

• INSERT INTO table_name is the command that adds a new row into a table named `table_name` in the MySQL database.
• (column_1,column_2,…) are the column names in which a new record will be added.
• VALUES (value_1,value_2,…) specifies the values to be added into the new row.

While adding new records, we need to be careful about the defined data types while creating the structure of the table.

• String: All string values should be enclosed with single quotes.
• Numeric: Numeric values should not be enclosed within either single or double-quotes.
• Date: Those data types should be in the single quote with the ‘YYYY-MM-DD’ format.

Let’s consider there is an ‘employee’ table consisting of the following attributes:

If we are adding values for all the columns of a table, we don’t need to specify the column names in the query, but we have to make sure that our new record should follow the sequence of the column as defined in the table.

INSERT INTO employee
VALUES (1004, ’Ravi’, 9856478398, ‘ravi@gmail.com’, ‘marketing’, ‘shiv’, ‘kolkata’);

If we don’t have all the values of columns and we will insert some of them, then we have to specify the column names in the query.

INSERT INTO employee (emp_id, emp_name, phone, email, manager)
VALUES (1005, ’sam’, 9856478398, ‘sam@gmail.com’, ‘shivankar’);

Implementation of Insert Command in MySQL

Let’s consider that we have the following lists of new records that are needed to add to the STUDENT database table.

We will insert those rows one by one into the database.

• Let’s start with Sandeep. Here ‘Roll_no’, ‘Standard’, ‘Phone’ and ‘Percentage’ are numeric fields, so values in this column will be inserted without quotes.

INSERT INTO student
VALUES (1, ‘Sandeep’, ‘Kumar’, 10, 9876456726, 89.33, ‘Cuttack’);

Note: As we have values of all the columns of the student table, we don’t need to specify the column name while using the INSERT command. But we have to make sure that our value should follow the order of columns in the table.

• In the case of Shyam record, we can see many values are missing. So here we need to specify the column names, in which we want to insert the values.

INSERT INTO student (Roll_no, First_name, Standard, Percentage, City)
VALUES (2, ‘Shyam’, 9, 76, ‘Bhubaneswar’);

Note: In this record, we don’t have the values of every column. That’s why we need to specify all the column names in which we want to insert our values and in the order of those column names, we have to mention values as well.

INSERT INTO student (Roll_no, First_name, Last_name, Phone, Percentage)
VALUES (3, ‘Sakti’,’naik’, 6463765776, 76);

INSERT INTO student (Roll_no, First_name, Standard, Phone, City)
VALUES (4, ‘Sid’, 8, 9864876986, ‘Kolkata’);

INSERT INTO student (Roll_no, First_name, Last_name,standard, Percentage)
VALUES (5, ‘Vinay’,’Kumar’, 10, 92);

Changing the order of the columns & value will have no effect on the INSERT query as the right value can’t be mapped to the right column. Thus problems like inserting a numeric value into a string or vice versa may arise.

In all the above queries, so many fields like Last_name, Phone, City values are missing. So in such cases, MySQL will insert by default NULL values in those columns which we have skipped in the queries.

Inserting into a Table from Another Table

If there are 2 similar tables, and we want to insert data from table_1 to table_2 directly to avoid manual work, we can also use a different type of INSERT query. In such a case, we have to use a SELECT command with the INSERT command. Basically SELECT command comes under DQL (Data Query Language), which is used for retrieval/fetching of data. The select command can be used with many types of clauses also.

The basic syntax for inserting records to one table from another is as follows:

INSERT INTO table_1 SELECT * FROM table_2;

Example

Let us see the example given below:

1. Order Table

2. Order_archive Table

Here we have 2 tables named Order and Order_archive. If we will move all records from the Order table to Order_archive, then the following code can perform the task:

INSERT INTO Order_archive SELECT * FROM Order;

If we want to move some specific columns from the Order table to Order_archive, then:

INSERT INTO Order_archive (Order_no, Order_department, Contact_no, Quantity)
SELECT Order_no, Order_department, Contact_no, Quantity FROM Order;

Conclusion

Insert command is very useful as it comes to play from the time of table creation to every single time whenever we add more records to the existing table.  We can summarize the following points from this article:

• INSERT command is used to add new data to the table.
• The date and string value should be in a single quote.
• The numeric value should not be in quotes.
• While inserting records in specific columns, the column name and value should be mapped in the same order.
• This command can also be used to insert records from one table to another table.

Recommended Articles

This is a guide to Insert in MySQL. Here we discuss the basic concept, Implementation of Insert Command in MySQL along with appropriate examples and syntax. You may also look at the following article –

1. MySQL String functions
2. What is MySQL?
3. Conditional Operators in MySQL
4. NOT in MySQL