5 Easy Ways to Insert Multiple Rows in SQL

Inserting multiple rows in SQL can be a daunting task, especially for those who are new to database management. However, with the right techniques and a solid understanding of SQL syntax, you can efficiently add multiple rows to your database tables. In this article, we will explore five easy ways to insert multiple rows in SQL, providing you with practical examples and expert insights to help you master this essential skill.

As a database professional with over a decade of experience in designing and optimizing database systems, I can attest that inserting multiple rows in SQL is a common task that can be accomplished using various methods. Whether you're working with a small dataset or a large-scale database, understanding these techniques will help you streamline your workflow and improve your productivity.

Method 1: Using the INSERT INTO Statement with Multiple VALUES

One of the most straightforward ways to insert multiple rows in SQL is by using the INSERT INTO statement with multiple VALUES. This method allows you to specify multiple rows of data in a single statement, separated by commas.

Here's an example:

INSERT INTO customers (name, email, phone)
VALUES
('John Doe', 'john.doe@example.com', '123-456-7890'),
('Jane Smith', 'jane.smith@example.com', '987-654-3210'),
('Bob Johnson', 'bob.johnson@example.com', '555-123-4567');

In this example, we're inserting three rows of data into the customers table, specifying the name, email, and phone columns for each row.

Benefits and Limitations

This method is easy to use and efficient for small to medium-sized datasets. However, it can become cumbersome for large datasets, and errors can occur if the data is not properly formatted.

Method 2: Using a SELECT Statement with UNION

Another way to insert multiple rows in SQL is by using a SELECT statement with UNION. This method allows you to combine multiple SELECT statements into a single result set, which can then be inserted into your table.

Here's an example:

INSERT INTO customers (name, email, phone)
SELECT 'John Doe', 'john.doe@example.com', '123-456-7890'
UNION
SELECT 'Jane Smith', 'jane.smith@example.com', '987-654-3210'
UNION
SELECT 'Bob Johnson', 'bob.johnson@example.com', '555-123-4567';

In this example, we're using three SELECT statements with UNION to combine the data into a single result set, which is then inserted into the customers table.

Benefits and Limitations

This method is useful for inserting data from multiple sources or for creating a temporary result set. However, it can be slower than other methods, and the UNION operator can be limited in its functionality.

Method 3: Using a VALUES Table

In some databases, such as SQL Server, you can use a VALUES table to insert multiple rows. This method allows you to define a table-like structure with multiple rows of data, which can then be inserted into your table.

Here's an example:

INSERT INTO customers (name, email, phone)
VALUES 
    ('John Doe', 'john.doe@example.com', '123-456-7890'),
    ('Jane Smith', 'jane.smith@example.com', '987-654-3210'),
    ('Bob Johnson', 'bob.johnson@example.com', '555-123-4567');

In this example, we're using a VALUES table to define three rows of data, which are then inserted into the customers table.

Benefits and Limitations

This method is efficient for large datasets and provides a flexible way to insert data. However, it may not be supported in all databases, and the syntax can vary.

Method 4: Using a Common Table Expression (CTE)

A Common Table Expression (CTE) is a temporary result set that can be used within a SELECT, INSERT, or UPDATE statement. You can use a CTE to insert multiple rows into a table.

Here's an example:

WITH customers_to_insert AS (
    SELECT 'John Doe', 'john.doe@example.com', '123-456-7890'
    UNION
    SELECT 'Jane Smith', 'jane.smith@example.com', '987-654-3210'
    UNION
    SELECT 'Bob Johnson', 'bob.johnson@example.com', '555-123-4567'
)
INSERT INTO customers (name, email, phone)
SELECT name, email, phone
FROM customers_to_insert;

In this example, we're using a CTE to define a temporary result set with three rows of data, which is then inserted into the customers table.

Benefits and Limitations

This method provides a flexible way to insert data and can be used with complex queries. However, it may have performance implications and requires careful planning.

Method 5: Using a BULK INSERT Statement

A BULK INSERT statement allows you to insert large amounts of data into a table from a file or other data source. This method is efficient for large datasets and provides a flexible way to insert data.

Here's an example:

BULK INSERT customers
FROM 'C:\data\customers.csv'
WITH
(
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n'
);

In this example, we're using a BULK INSERT statement to insert data from a CSV file into the customers table.

Benefits and Limitations

This method is efficient for large datasets and provides a flexible way to insert data. However, it requires careful planning and may have security implications.