How to Insert Multiple Values in a Single Query in SQL Server

In this tutorial you will learn how to add multiple values into a table in a single query. Being able to add multiple values in a single SQL query is a lot more convenient than adding one by one and is just as easy.  We will use three keywords to make this work: INSERT INTO, SELECT, and UNION ALL.

Before we start, you should create a table called Employees as shown below:



The INSERT INTO statement is used to insert a new row in a table. When using this keyword we are specifying what table we want to insert values into, for example: INSERT INTO table_name.

After declaring what table we want to insert values into, we declare the columns from the table in parenthesis as such: INSERT INTO table_name (column1, column2, column3,…).



Below the INSERT INTO statement we will use the SELECT statement. The SELECT statement is used to select data from a database. The result is stored in a result table called the result-set. In the SELECT statement we can add employee data for the columns specified in the INSERT INTO statement. Just think of SELECT as:
SELECT ‘FirstName’, ‘LastName’, ‘HireDate’, MgrId, Ssn, Salary.

So in reality after we put in actual data it will look like the code below:



Under the SELECT statement we will use the UNION ALL statement. The UNION operator is used to combine the result-set of two or more SELECT statements. Keep in mind the UNION operator selects only distinct values by default. To allow duplicate values, we use UNION ALL. Take note of the code below:



It is a rinse and repeat process with the SELECT and UNION ALL statements. Click the execute button to successfully execute the query. Next we want to output the table. We can easily do this with a SQL query as shown below:



Click the execute button and you should see a table with three employees and their information.

Thanks for reading and make sure to download the source files to get a better understanding of how the code works.

Leave a Reply

Your email address will not be published. Required fields are marked *