Learning Objective
The objective of this SQL Server tutorial is to teach you how to use the INSERT statement to enter a record (i.e. row of information) 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.
SQL Server INSERT Syntax
The basic syntax of SQL Server INSERT clause to enter a single record into a table is as follows.
INSERT INTO table [column list] VALUES [value list]
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 Example
- 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.
Let us first create a table so that we can subsequently populate it with data using the INSERT statement and understand its workings. We are creating a table called customers which will contain personally identifiable customer information using the CREATE TABLE command.
CREATE TABLE customers (cust_id INT IDENTITY(1,1) PRIMARY KEY NOT NULL, first_name VARCHAR(30) NOT NULL, last_name VARCHAR(30) NOT NULL, cust_phone CHAR(10), cust_email VARCHAR(50), cust_location VARCHAR(30) not null);
After creating the table, we can check whether the table has been created or not using the below command in SQL Server.
SELECT * FROM information_schema.tables;
It will show the list of tables in the database with the newly created table in it (as highlighted below).
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE |
| master | dbo | spt_fallback_db | BASE TABLE |
| master | dbo | spt_fallback_dev | BASE TABLE |
| master | dbo | spt_fallback_usg | BASE TABLE |
| master | dbo | customers | BASE TABLE |
| master | dbo | spt_values | VIEW |
| master | dbo | spt_monitor | BASE TABLE |
| master | dbo | MSreplication_options | BASE TABLE |
| master | dbo | spt_fallback_dev | BASE TABLE |
Now we will insert a record into the table using the below INSERT statement.
NOTE: The first primary key column cust_id is of IDENTITY datatype which will be automatically populated with incremental values and hence does not figure in the query.
INSERT INTO customers (first_name, last_name, cust_phone, cust_email, cust_location)
VALUES ('Aditya', 'Singh', '9154299793', 'adi919@hotmail.com', 'Delhi');
We can check whether the insertion has been successful using SELECT query.
SELECT * FROM customers;
The output will show the first new record.
| cust_id | first_name | last_name | cust_phone | cust_email | cust_location |
| 1 | Aditya | Singh | 9154299793 | adi919@hotmail.com | Delhi |
Let us insert 2 more records one by one so that we have 3 records in the table.
INSERT INTO customers (first_name, last_name, cust_phone, cust_email, cust_location) VALUES ('Sek', 'Ismile', '9900917729', 'sek_2222@gmail.com', 'Lucknow');
INSERT INTO customers (first_name, last_name, cust_phone, cust_email, cust_location) values ('Priya', 'Upala', '8595771783', 'pu_up_pp@gmail.com', 'Bangalore');
After insert if we run SELECT query, we will get the below output showing all 3 records. Likewise, we can continue to add records to the table as and when required one at a time.
| 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 |