MySQL INSERT INTO Statement

In DBMS, CRUD operations (Create, Read, Update, Delete) are fundamental for managing data effectively. Among these, the Create operation, which involves inserting new data into a database, plays a crucial role.

In this article, Let’s explore how the INSERT INTO statement works and its benefits in the realm of CRUD operations.

INSERT INTO Statement

The MySQL INSERT INTO Statement is used to insert new records or rows into a table. MySQL Insert into Statement enables us to work with databases to add all the new values.

Let’s look at Various ways of using the INSERT INTO statement, exploring how it can be used to add data efficiently to our table.

1. MySQL INSERT INTO table Single Row
2. MySQL INSERT INTO table Multiple Rows
3. MySQL INSERT INTO SELECT
4. MySQL INSERT INTO DATE

Syntax

There are two ways to write an INSERT INTO statement in MySQL.

1. When adding values to all the columns of the table.

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

2. When adding values to specific columns of the table:

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

Demo MySQL Database

Write the following command to create a empty table on which we will be perform the INSERT INTO.

CREATE TABLE employees (
name VARCHAR(50),
age INT
department VARCHAR(50);

INSERT Statement Examples

Let’s look at the examples of INSERT INTO statement, to understand the working of INSERT INTO statement, and cover it’s different use cases.

Example 1: MySQL INSERT INTO table Single Row

To insert a single record into a table, specify the table name and provide values for each column in the order they appear in the table.

Syntax:

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

Query:

INSERT INTO employees (name, age, department)
VALUES ('John Doe', 30, 'IT');

Output:

name	age	department
John Doe	30	IT

Explanation: This syntax inserts a single row of data into the ‘employees‘ table. You specify the table name and the columns into which you want to insert data, followed by the corresponding values. In this example, we insert the name ‘John Doe’, age ’30’, and department ‘IT’ into the respective columns.

Example 2: MySQL INSERT INTO table Multiple Rows

MySQL allows inserting multiple rows of data in a single query. It is reducing the overhead of executing multiple queries. The syntax is similar to inserting a single row but with multiple sets of values enclosed within parentheses and separated by commas

Syntax:

INSERT INTO table_name (column1, column2, column3, ...)
VALUES 
(value1_1, value1_2, value1_3, ...),
(value2_1, value2_2, value2_3, ...),
(value3_1, value3_2, value3_3, ...);

Query:

INSERT INTO employees (name, age, department)
VALUES
('Jane Smith', 28, 'HR'),
('Michael Johnson', 35, 'Finance'),
('Emily Brown', 32, 'Marketing');

Output:

name	age	department
Jane Smith	28	HR
Michael Johnson	35	Finance
Emily Brown	32	Marketing

Explanation: This syntax allows you to insert multiple rows of data into the ‘employees‘ table in a single query. Each set of values represents a separate row to be inserted. In this example, we insert three new employees into the table with their respective details.

Example 3: MySQL INSERT INTO SELECT

Sometimes, We may need to insert data into a table based on the results of a SELECT query. MySQL facilitates this through the INSERT INTO SELECT statement. This statement selects data from one table and inserts it into another

Syntax:

INSERT INTO table_name (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM another_table
WHERE condition;

Query:

INSERT INTO employees_backup (name, age, department)
SELECT name, age, department
FROM employees
WHERE age > 30;

Output:

name	age	department
Michael Johnson	35	Finance
Emily Brown	32	Marketing

Explanation: This syntax inserts data into the ‘employees_backup‘ table based on the results of a SELECT query. In this example, we select employees from the ’employees’ table where the age is greater than 30 and insert their details into the ’employees_backup’ table.

Example 4: MySQL INSERT INTO DATE

Inserting date values into a MySQL database is a common requirement. MySQL provides several date and time functions to handle date-related operations efficiently. To insert the current date into a date column, we can use the NOW() function with MySQL Insert Date

Syntax:

INSERT INTO table_name (date_column)
VALUES (NOW());

Query:

INSERT INTO orders (order_date)
VALUES (NOW());

Output:

2024-02-20 12:00:00

Explanation:

To insert the current date into a date column, you can use the NOW() function within the INSERT INTO statement. This example inserts the current date into the ‘order_date’ column of the ‘orders’ table.

Key TakeAways:

• It’s used to add new data to a database table.
• Supports inserting both single and multiple rows of data in one command.
• Different syntax options are available for specifying the data to insert, including directly specifying values or inserting data from a query.
• Proper use of the statement is crucial for maintaining database integrity and ensuring accurate data entry.