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:
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,…).
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:
Thanks for reading and make sure to download the source files to get a better understanding of how the code works.
InsertMultipleValues.zip
Before we start, you should create a table called Employees as shown below:
Code Block
CreateTableIdentity.sqlce
Create a tabled named Employees.CREATE TABLE Employees
(
EmpId int NOT NULL IDENTITY,
FirstName nvarchar(30) NOT NULL,
LastName nvarchar(30) NOT NULl,
HireDate datetime NOT NULL,
MgrId int NULL,
Ssn nvarchar(20) NOT NULL,
Salary money NOT NULL
)
We moved our web sites to Server Intellect and have found them to be incredibly professional. Their setup is very easy and we were up and running in no time.
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,…).
Code Block
InsertMultipleValues.sqlce
We declare Employees as the table and include it’s columns.INSERT INTO Employees (FirstName, LastName, HireDate, MgrId, Ssn, Salary)
We used over 10 web hosting companies before we found Server Intellect. Our new server with cloud hosting,was set up in less than 24 hours. We were able to confirm our order over the phone. They responded to our inquiries within an hour. Server Intellect’s customer support and assistance are the best we’ve ever experienced.
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:
Code Block
InsertMultipleValues.sqlce
Using the SELECT statement.SELECT 'Jim', 'Johnson', '20110101 12:00:00:000', 21, 123456789, 32000
Code Block
InsertMultipleValues.sqlce
Make sure to use UNION ALL.SELECT 'Jim', 'Johnson', '20110101 12:00:00:000', 21, 123456789, 32000
UNION ALL
SELECT 'Kim', 'Tyra', '20110101 12:00:00:000', 21, 112345678, 25000
UNION ALL
SELECT 'Ryu', 'Drake', '20110101 12:00:00:000', 21, 112234567, 24000
Code Block
SelectAll.sqlce
Select all columns from table Employees.SELECT * FROM Employees
I just signed up at Server Intellect and couldn’t be more pleased with my fully scalable & redundant cloud hosting! Check it out and see for yourself.
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.
InsertMultipleValues.zip
