Have you ever wondered how to insert multiple values into a table using only one insert statement in SQL Server? In this article, we will explore a simple and efficient method to achieve this.

Traditionally, when we need to insert multiple records into a table, we would use the INSERT INTO statement multiple times, like this:

INSERT INTO MyTable (FirstCol, SecondCol)
VALUES ('First', 1);

INSERT INTO MyTable (FirstCol, SecondCol)
VALUES ('Second', 2);

INSERT INTO MyTable (FirstCol, SecondCol)
VALUES ('Third', 3);

INSERT INTO MyTable (FirstCol, SecondCol)
VALUES ('Fourth', 4);

INSERT INTO MyTable (FirstCol, SecondCol)
VALUES ('Fifth', 5);

While this method works, it can be repetitive and time-consuming, especially when dealing with a large number of records. Fortunately, there is a more efficient alternative using the UNION ALL and INSERT INTO…SELECT… clauses.

Here’s how it works:

INSERT INTO MyTable (FirstCol, SecondCol)
SELECT 'First', 1
UNION ALL
SELECT 'Second', 2
UNION ALL
SELECT 'Third', 3
UNION ALL
SELECT 'Fourth', 4
UNION ALL
SELECT 'Fifth', 5;

By using the UNION ALL operator, we can combine multiple SELECT statements into a single result set, which can then be inserted into the table using the INSERT INTO statement. This approach not only saves time but also keeps our focus on the task at hand, rather than repetitive copy-pasting.

It’s important to note that there is no significant performance difference between the two methods. However, if performance is a concern, it is recommended to use the UNION ALL approach for one-time insert scripts.

So, the next time you need to insert multiple values into a table in SQL Server, consider using the UNION ALL and INSERT INTO…SELECT… method for a more efficient and streamlined approach.

For more information on SQL Server concepts and performance optimization, check out our other articles on our blog.