Learning Objective
The objective of this SQL Server tutorial is to teach you how to use the INSERT statement to enter multiple records (i.e. rows of information) with a single query into a new or existing table.
What is INSERT in SQL Server?
INSERT is the most basic of all SQL queries. It is the first query to be executed post table creation for entering records into a table. Every other DML query becomes applicable and follows the INSERT query. It is important to note and keep in mind that INSERT enters values in all the columns in a table row. It cannot be used to enter data in some columns in a row leaving out the other columns. Selective insertion of data is not possible.
Operation
In its most simple form, the INSERT statement is used to enter a single row of information into a table by explicitly specifying the values for the different columns. However, in this tutorial we will see how we can insert multiple rows of information into the table with the column values explicitly specified.
Syntax
The basic syntax of SQL Server INSERT statement to enter multiple records into a table is as follows.
INSERT INTO table [column list] VALUES [value list 1], [value list 2],……,[value list N];
In this syntax,
- INSERT – clause used to insert a row or rows of data into a new or existing table.
- INTO – keyword used with INSERT to specify the table into which data should be entered.
- column list – the list of all columns or fields in the table which need to be populated with the provided data.
- VALUES – keyword used to specify the values to be entered for the columns in the table.
- value list – the list of values, one for each column separated by comma.
SQL Server INSERT Examples
- Columns are also referred to as fields or attributes and the terms are used interchangeably.
- A row of information in a table is called a tuple.
Suppose we have a customer table containing 3 customer records in it as below.
| cust_id | first_name | last_name | cust_phone | cust_email | cust_location |
| 1 | Aditya | Singh | 9154299793 | adi919@hotmail.com | Delhi |
| 2 | Sek | Ismile | 9900917729 | sek_2222@gmail.com | Lucknow |
| 3 | Priya | Upala | 8595771783 | pu_up_pp@gmail.com | Bangalore |
We can insert 3 more records into the table at the same time with the below single INSERT query.
INSERT INTO customers
(first_name, last_name, cust_phone, cust_email, cust_location)
VALUES
('Vikas', 'Malagi', '9774012672', 'vk07mh@gmail.com', 'Pune'),
('Pallab', 'Chakraborty', '7899301215', 'pchakkol_@yahoo.co.in', 'Kolkata'),
('Nidhi', 'Rohatgi', '9959239910', 'roni_9619@gmail.com', 'Delhi');
We can check whether the insertion has been successful using SELECT query.
SELECT * FROM customers;
The output generated will show all the 6 records as below. Likewise, we can add multiple records at the same time with a single INSERT query as and when required.
| cust_id | first_name | last_name | cust_phone | cust_email | cust_location |
| 1 | Aditya | Singh | 9154299793 | adi919@hotmail.com | Delhi |
| 2 | Sek | Ismile | 9900917729 | sek_2222@gmail.com | Lucknow |
| 3 | Priya | Upala | 8595771783 | pu_up_pp@gmail.com | Bangalore |
| 4 | Vikas | Malagi | 9774012672 | vk07mh@gmail.com | Pune |
| 5 | Pallab | Chakraborty | 7899301215 | pchakkol_@yahoo.co.in | Kolkata |
| 6 | Nidhi | Rohatgi | 9959239910 | roni_9619@gmail.com | Delhi |