How to insert values to identity column in SQL Server.

Anonymous User 1750 03 Mar 2016

We want to insert values to identity column in SQL Server. How to do this Please Help me.

Anonymous User

Other

I am a content writter !

1 Answers

Anonymous User 03 Mar 2016

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

forum

How to insert values to identity column in SQL Server.

Can you answer this question?

1 Answers