SQL INSERT Multiple Rows

Introduction

In this chapter, we will learn how to use the INSERT statement to add multiple rows of data into a table in a single statement. This is a more efficient way to insert multiple rows compared to inserting each row individually. We will cover the syntax, provide examples, and demonstrate how to handle multiple row insertions effectively.

Syntax for Inserting Multiple Rows

To insert multiple rows into a table in a single INSERT statement, you can provide multiple sets of values, each enclosed in parentheses and separated by commas.

Basic Syntax

INSERT INTO table_name (column1, column2, column3, ...)
VALUES
(value1a, value2a, value3a, ...),
(value1b, value2b, value3b, ...),
(value1c, value2c, value3c, ...);

• table_name: The name of the table where you want to insert data.
• column1, column2, ...: The columns in the table where you want to insert data.
• value1a, value2a, ...: The values for each row to be inserted.

Step-by-Step Example

1. Create a Sample Table

First, we will create a sample table named employees.

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100)
);

2. Insert Multiple Rows

Next, we will insert multiple rows into the employees table in a single INSERT statement.

INSERT INTO employees (first_name, last_name, email)
VALUES
('Ramesh', 'Kumar', 'ramesh.kumar@example.com'),
('Sita', 'Patel', 'sita.patel@example.com'),
('Arjun', 'Singh', 'arjun.singh@example.com'),
('Priya', 'Sharma', 'priya.sharma@example.com');

3. Verify the Insertions

To verify that the rows were inserted correctly, we can use the SELECT statement to retrieve all rows from the employees table.

SELECT * FROM employees;

Output

id	first_name	last_name	email
1	Ramesh	Kumar	ramesh.kumar@example.com
2	Sita	Patel	sita.patel@example.com
3	Arjun	Singh	arjun.singh@example.com
4	Priya	Sharma	priya.sharma@example.com

Using Default Values for Some Columns

If you want to insert rows where some columns should use their default values, you can omit these columns from the INSERT statement.

Example

Assume the employees table has a hire_date column with a default value of the current date.

ALTER TABLE employees
ADD hire_date DATE DEFAULT CURRENT_DATE;

Now, we will insert multiple rows without specifying the hire_date column.

INSERT INTO employees (first_name, last_name, email)
VALUES
('Anil', 'Mehta', 'anil.mehta@example.com'),
('Deepa', 'Verma', 'deepa.verma@example.com');

Verify the Insertions

SELECT * FROM employees;

Output

id	first_name	last_name	email	hire_date
1	Ramesh	Kumar	ramesh.kumar@example.com	2024-01-01
2	Sita	Patel	sita.patel@example.com	2024-01-01
3	Arjun	Singh	arjun.singh@example.com	2024-01-01
4	Priya	Sharma	priya.sharma@example.com	2024-01-01
5	Anil	Mehta	anil.mehta@example.com	2024-01-01
6	Deepa	Verma	deepa.verma@example.com	2024-01-01

Inserting Multiple Rows Without Specifying Column Names

You can insert multiple rows without specifying column names if you provide values for all columns in the same order they are defined in the table.

Example

INSERT INTO employees
VALUES
(7, 'Rajesh', 'Nair', 'rajesh.nair@example.com', '2024-01-01'),
(8, 'Neha', 'Kapoor', 'neha.kapoor@example.com', '2024-01-01');

Verify the Insertions

SELECT * FROM employees;

Output

id	first_name	last_name	email	hire_date
1	Ramesh	Kumar	ramesh.kumar@example.com	2024-01-01
2	Sita	Patel	sita.patel@example.com	2024-01-01
3	Arjun	Singh	arjun.singh@example.com	2024-01-01
4	Priya	Sharma	priya.sharma@example.com	2024-01-01
5	Anil	Mehta	anil.mehta@example.com	2024-01-01
6	Deepa	Verma	deepa.verma@example.com	2024-01-01
7	Rajesh	Nair	rajesh.nair@example.com	2024-01-01
8	Neha	Kapoor	neha.kapoor@example.com	2024-01-01

Conclusion

Inserting multiple rows in a single INSERT statement is an efficient way to add data to a table. This chapter covered the syntax and examples for inserting multiple rows, using default values, and inserting rows without specifying column names. Understanding how to insert multiple rows effectively can significantly improve the performance of your database operations. In the next chapter, we will explore how to retrieve data using the SELECT statement.