In MySQL, the INSERT INTO statement is essential for adding new data rows to a table in a database. This is important for setting up initial data in tables and for adding new records as needed when working with the database.

Understanding how to use the INSERT INTO statement is key for managing and ensuring the integrity and functionality of the database.

INSERT INTO Statement

The INSERT INTO statement in MySQL is a Data Manipulation Language (DML) command that allows users to add new records (rows) into a specified table. It follows a concise syntax to specify the table name and the values to be inserted into the respective columns.

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. Insert record in a single row
2. Insert record in multiple rows

Syntax:

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

1. The general syntax for inserting a single record into a MySQL table using the SQL INSERT INTO command is as follows:

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

2. Use the following statement to insert multiple records with a single command:

INSERT INTO table_name (column1, column2, ..., columnN)
VALUES
    (value1_1, value1_2, ..., value1_N),
    (value2_1, value2_2, ..., value2_N),
    ...,
    (valueM_1, valueM_2, ..., valueM_N);

Demo MySQL Database

Write the following command to create an empty table on which we will 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 the INSERT INTO statement, to understand the workings of INSERT INTO statement, and cover its 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 single record into the employees table 
INSERT INTO employees (name, age, department)
VALUES ('John Doe', 30, 'IT');

-- Select all records from the employees table 
select * from employees;

Output:

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, ...);

-- Insert multiple records into the employees table
INSERT INTO employees (name, age, department)
VALUES
    ('Jane Smith', 28, 'HR'),
    ('Michael Johnson', 35, 'Finance'),
    ('Emily Brown', 32, 'Marketing');

-- Select all records from the employees table
SELECT * FROM employees;

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

-- Create the employees table
CREATE TABLE employees (
    name VARCHAR(50),
    age INT,
    department VARCHAR(50)
);

-- Insert multiple records into the employees table
INSERT INTO employees (name, age, department)
VALUES
    ('Jane Smith', 28, 'HR'),
    ('Michael Johnson', 35, 'Finance'),
    ('Emily Brown', 32, 'Marketing');

-- Create the employees_backup table
CREATE TABLE employees_backup (
    name VARCHAR(50),
    age INT,
    department VARCHAR(50)
);

-- Insert records into the employees_backup table where age > 30
INSERT INTO employees_backup (name, age, department)
SELECT name, age, department
FROM employees
WHERE age > 30;

-- Select all records from the employees table
SELECT * FROM employees_backup;

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

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

2024-02-20 12:00:00