Introduction to Insert in MySQL
The main goal of a database system is to store data in tables. We have different kinds of SQL commands to define the database’s functionality. 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. The merits of using these commands are if any wrong changes happen, we can roll back/undo it.
The following are the commands in DML:
1. INSERT: Used to insert new rows into the table.
INSERT into employee
Values(101,'abcd');2. DELETE: Delete a table’s single row or entire records.
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 to the new row.
While adding new records, we need to be careful about the defined data types while creating the table structure.
- String: All string values should be enclosed with single quotes.
- Numeric: Numeric values should not be enclosed within single or double quotes.
- Date: Those data types should be in a single quote with the ‘YYYY-MM-DD’ format.
Let’s consider there is an ’employee’ table consisting of the following attributes:
| Emp_id | Emp_name | Phone | Department | Manager | City | |
| 1001 | Vinay | 9876543219 | [email protected] | CSR | Sudhir | Bangalore |
| 1002 | Raaj | 9764527848 | [email protected] | IT | Stephen | Hyderabad |
| 1003 | Sakti | 9567382784 | [email protected] | Automotive | Ved | Bhubaneswar |
If we add values for all the table columns, we don’t need to specify the column names in the query, but we have to ensure that our new record follows the column sequence as defined in the table.
INSERT INTO employee
VALUES (1004, 'Ravi', 9856478398, '[email protected]', '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, '[email protected]', 'shivankar');Implementation of Insert Command in MySQL
Let’s consider the following lists of new records to add to the STUDENT database table.
| Roll_no | First_name | Last_name | Standard | Phone | Percentage | City |
| 1 | Sandeep | Kumar | 10 | 9876456726 | 89.33 | Cuttack |
| 2 | Shyam | 9 | 76 | Bhubaneswar | ||
| 3 | Sakti | Naik | 6463765776 | 76 | ||
| 4 | Sid | 8 | 9864876986 | Kolkata | ||
| 5 | Vinay | Kumar | 10 | 92 |
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 ensure that our value follows the order of columns in the table.
- In the case of the Shyam record, we can see many values are missing. So here, we need to specify the column names 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 & values will not affect 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.
Many fields like Last_name, Phone, and City values are missing in the above queries. 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 two 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 must use a SELECT command with the INSERT command. The SELECT command comes under DQL (Data Query Language), which is used for the 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
| Order_no | Order_department | Customer_name | Contact_no | Quantity | Place |
| 8465637 | Furniture | Peter | 8659876766 | 1000 | Delhi |
| 9473636 | Ornaments | Alex | 9863769898 | 800 | Mumbai |
2. Order_archive Table
| Order_no | Order_department | Customer_name | Contact_no | Quantity | Place |
Here we have two tables named Order and Order_archive. If we 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 insert records from one table to another.
Recommended Articles
We hope that this EDUCBA information on “Insert in MySQL” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
