Inserting multiple rows in a single SQL query?

Efficiently Inserting Multiple Rows in a Single SQL Query

Learn various SQL techniques to insert multiple rows into a database table with a single query, optimizing performance and simplifying your code.

Inserting data into a database is a fundamental operation. While inserting a single row is straightforward, applications often need to insert many rows at once. Performing individual INSERT statements for each row can be inefficient due to the overhead of multiple network round trips and transaction management. This article explores several methods to insert multiple rows using a single SQL query, focusing on common SQL dialects like SQL Server, MySQL, and PostgreSQL.

The VALUES Clause for Multiple Rows

The most common and widely supported method for inserting multiple rows in a single statement is by providing multiple sets of values within the VALUES clause. Each set of parentheses represents a single row, and the sets are separated by commas. This approach is highly readable and efficient for a moderate number of rows.

INSERT INTO Products (ProductName, Price, Quantity)
VALUES
    ('Laptop', 1200.00, 50),
    ('Mouse', 25.00, 200),
    ('Keyboard', 75.00, 150);

Inserting multiple rows using the VALUES clause

Inserting from a SELECT Statement

Another powerful technique is to insert rows that are the result of a SELECT query. This is particularly useful when you need to copy data from one table to another, or when you're generating data dynamically from existing tables. This method is highly efficient as the database can optimize the data transfer internally.

INSERT INTO ArchiveProducts (ProductID, ProductName, Price)
SELECT ProductID, ProductName, Price
FROM Products
WHERE Quantity = 0;

Inserting data into ArchiveProducts from Products table

flowchart TD
    A[Source Table] --> B{SELECT Query}
    B --> C[Target Table]
    C --> D[INSERT Operation]
    D --> E[Multiple Rows Added]

Flow of inserting data from a SELECT statement

Using UNION ALL with SELECT (SQL Server, Oracle)

For scenarios where you need to insert a fixed set of literal values, but prefer the SELECT syntax, you can combine multiple SELECT statements using UNION ALL. Each SELECT statement provides a single row of data. This approach is often used when constructing dynamic SQL or when the VALUES clause syntax is less convenient for programmatic generation.

INSERT INTO Employees (FirstName, LastName, Department)
SELECT 'John', 'Doe', 'Sales'
UNION ALL
SELECT 'Jane', 'Smith', 'Marketing'
UNION ALL
SELECT 'Peter', 'Jones', 'IT';

Inserting multiple rows using SELECT and UNION ALL

Performance Considerations

Choosing the right method for inserting multiple rows can significantly impact performance. Generally, methods that allow the database to process data in a single batch are more efficient. This reduces transaction overhead, logging, and network latency. Always prefer a single INSERT statement with multiple rows over multiple individual INSERT statements.

pie title "Performance Impact of Insert Methods"
    "Single INSERT (Multiple VALUES)" : 40
    "INSERT from SELECT" : 35
    "INSERT with UNION ALL" : 20
    "Multiple Individual INSERTs" : 5

Relative performance impact of different multi-row insert methods