How to Efficiently Insert Multiple Rows in a Single SQL Query

1. Introduction

Sometimes we need to INSERT multiple rows of data into a table with a single SQL query. This simplifies the process and improves performance by reducing the number of database interactions.

In this tutorial, we’ll use the Baeldung University schema to explore various techniques for doing this efficiently.

2. Importance of Efficient Multiple Row Insertion in SQL

We often need to INSERT multiple rows of data into a database table. Doing this one row at a time can be inefficient and time-consuming, especially when dealing with large datasets.

Knowing how to INSERT multiple rows in a single SQL query is important because it simplifies the process and improves performance by reducing the number of database interactions.

Let’s review three different ways; the last one is specific to PostgreSQL.

3. Using INSERT INTO Statement With Multiple VALUES Clauses

First, we can specify multiple sets of values within parentheses, separated by commas after the VALUES clause of the INSERT INTO statement. This is a straightforward approach for inserting multiple rows into a database table in a single SQL query.

Let’s take a look at the basic syntax:

INSERT INTO Table_name (column1, column2, ...)
VALUES (value1_row1, value2_row1, ...),
       (value1_row2, value2_row2, ...),
       ...
       (value1_rowN, value2_rowN, ...);

Using this, we can insert multiple rows with just one command.

So let’s see what this would look like for the Student table:

INSERT INTO Student (id, name, national_id)
VALUES (1001, 'John Liu', '123345566'),
       (1003, 'Rita Ora', '132345166'),
       (1007, 'Philip Lose', '321345566'),
       (1010, 'Samantha Prabhu', '3217165566');

As we can see, each set of values in parentheses represents one row of data. So, we’re adding three new rows in total.

Please note that in SQL Server, there may be a limitation on the number of rows allowed in the VALUES clause.

4. Using INSERT INTO Statement With a SELECT Clause

For the second one, let’s imagine that we also have a legacy table Old_student that has a similar schema to Student. Given that, we can SELECT data from Old_student and INSERT it into Student.

Instead of listing all the values directly in this method, we can instruct the database to SELECT the data from somewhere else and INSERT it into our table:

INSERT INTO Student (id, name, national_id)
SELECT id, name, national_id
FROM Old_student
WHERE graduation_date = '2024-06-15';

In this instance, we’re inserting data into the Student table by selecting specific data from the Old_student table, based on the condition that the graduation date is 2024-06-15.

This method proves invaluable especially when dealing with large datasets or when the data to be inserted requires filtering or manipulation before insertion.

5. Using UNION ALL in PostgreSQL

Lastly, UNION ALL is a SQL set operation that combines the results of two or more SELECT statements into a single result set. Each set of values represents one row of data to be inserted into the table.

In PostgreSQL, we can use UNION ALL to also create a unified INSERT statement:

INSERT INTO Student (id, name, national_id) 
VALUES (1001, 'John Liu', 123345566) 
UNION ALL 
VALUES (1003, 'Rita Ora', 132345166) 
UNION ALL 
VALUES (1007, 'Philip Lose', 321345566)
UNION ALL 
VALUES (1010, 'Samantha Prabhu', 3217165566)

In this case, we inserted multiple rows of data into the Student table using a single SQL query. We did this by specifying the values for each row using the VALUES clause, separated by the UNION ALL operator.

6. Conclusion

In this article, we explored various methods for efficiently inserting multiple rows in a single SQL query.

First, we discussed using the INSERT INTO statement with multiple VALUES clauses. Second, we examined employing the INSERT INTO statement with a SELECT clause. Last, we explored using the UNION ALL operator in PostgreSQL to combine multiple sets of values into a unified INSERT statement.