You are currently viewing How To Insert Data in SQL Server Table

How To Insert Data in SQL Server Table

How To Insert Data in SQL Server Table

INSERT INTO Statement is used to insert new records in a database table.

Syntax:

INSERT INTO TableName(Col1,Col2,Col3,…ColN)

VALUES(Val1,Val2,Val3,…ValN)

Col1, Col2, Col3, … , ColN are column names up to N number of columns, and Val1, Val2, Val3, …, ValN are values for the table columns Col1, Col2, Col3, … , ColN respectively.

Before we insert records into a table, we need to create a table first. So, we will create a table tblEmployee, and then we insert records into that table.

CREATE TABLE tblEmployee
(
          EmpID INT PRIMARY KEY,
          EmpFirstName NVARCHAR(20),
          EmpLastName NVARCHAR(20),
          EmpAge INT,
          EmpGender NVARCHAR(10),
          EmpEmail NVARCHAR(20),
          EmpSalary INT,
          EmpJoiningDate DATETIME,
          EmpCountry NVARCHAR(20),
          EmpCity NVARCHAR(20)
)
GO

Let’s insert new records into a table tblEmployee using INSERT INTO statement.

INSERT INTO Without Specifying Column Names

If you want to insert records for all the columns of a table, then there is no need to specify column names, as shown below.

Note: If you have IDENTITY Column in a table, then you cannot skip column names, Column names are mandatory in case of IDENTITY Column

INSERT INTO tblEmployee
VALUES
    (1,'john','dave',26,'male','john@gmail.com',26000,'2010-02-01','USA','Washington');

INSERT INTO tblEmployee
VALUES
    (2,'kamran','akmal',35,'male','kami@gmail.com',25000,'2011-01-01','Pakistan','Lahore'); 
    
INSERT INTO tblEmployee
VALUES
    (3,'umar','akmal',36,'male','umar@gmail.com',38000,'2015-01-01','Pakistan','Karachi'); 

Insert Multiple Rows With Single INSERT INTO Statement

Values for multiple rows can be inserted with single INSERT INTO Statement just by putting comma(,) between two rows values.

INSERT INTO tblEmployee
(
    EmpID,EmpFirstName,EmpLastName,EmpAge,EmpGender,EmpEmail,EmpSalary,EmpJoiningDate,EmpCountry,EmpCity 
)
VALUES
    (4,'hafeez','sheikh',25,'male','hafeez@gmail.com',25000,'2016-05-05','India','Mumbai'),
    (5,'amar','sidhu',36,'male','amar@gmail.com',25000,'2016-05-05','India','New Dehli'),
    (6,'simran','sidhu',25,'female','simran@gmail.com',33000,'2018-01-01','India','Mumbai')

INSERT INTO With Selected Columns

Values can be inserted into a table with selected columns. In the following example, we will insert rows with only EmpID, EmpFirstName, EmpGender, EmpEmail, and EmpSalary columns. NULL values will be inserted automatically for those columns for which we did not specify values.

INSERT INTO tblEmployee
(
    EmpID,EmpFirstName,EmpGender,EmpEmail,EmpSalary
)
VALUES
    (7,'diyana','female','diyana@gmail.com',50000)

INSERT INTO With SELECT Statement

We can insert new records with SELECT Statement as shown below.

INSERT INTO tblEmployee
(
    EmpID,EmpFirstName,EmpLastName,EmpAge,EmpGender,EmpEmail,EmpSalary,EmpJoiningDate,EmpCountry,EmpCity 
)
SELECT 8,'karishma','rai',27,'female','krai@gmail.com',70000,'2004-01-01','UK','London'

Recommended Readings