HOW TO INSERT VALUES TO IDENTITY COLUMN IN SQL SERVER.

Ankit Singh

Total Post:341

Points:2389
Posted by  Ankit Singh
 588  View(s)
Ratings:
Rate this:
We want to insert values to identity column in SQL Server. How to do this Please Help me.
  1. aditya kumar Patel

    Post:254

    Points:1810
    Re: How to insert values to identity column in SQL Server.

    You can alllow insert to the identity field by setting IDENTITY_INSERT ON for a specific table.

    Show below:

    SET IDENTITY_INSERT [Employee] ON

     

    You can also disalllow insert to the identity field by setting IDENTITY_INSERT OFF for a specific table.

    shown below:

    SET IDENTITY_INSERT [Employee] OFF

     

    For Example:

     

    CREATE TABLE [dbo].[Employee] (

    [EmpNo] INT IDENTITY (1, 1) NOT NULL,

    [EmpName] VARCHAR (50) NOT NULL,

    [Salary] DECIMAL (18) NOT NULL,

    [DeptName] VARCHAR (50) NOT NULL,

    [Designation] VARCHAR (50) NOT NULL,

    PRIMARY KEY CLUSTERED ([EmpNo] ASC)

     

     

     

     

    );

     

    SET IDENTITY_INSERT [Employee] ON

     

     

     

    INSERT INTO [Employee]([EmpNo],[EmpName],[Salary],[DeptName],[Designation]) VALUES(1,'Aditya',20000,'Development','Programmer')

    INSERT INTO [Employee]([EmpNo],[EmpName],[Salary],[DeptName],[Designation]) VALUES(2,'Anupam',30000,'Development','Programmer')

     

     

     

    SET IDENTITY_INSERT [Employee] OFF

    INSERT INTO [Employee]([EmpName],[Salary],[DeptName],[Designation]) VALUES('Pawan',40000,'Development','Programmer')

     

    select * from Employee

     

Answer

NEWSLETTER

Enter your email address here always to be updated. We promise not to spam!