Home > DeveloperSection > Forums > How to insert values to identity column in SQL Server.
Ankit Singh

Total Post:341

Points:2389
Posted on    March-03-2016 10:43 PM

 MSSQL Server SQL Server 
Ratings:


 1 Reply(s)
 305  View(s)
Rate this:
We want to insert values to identity column in SQL Server. How to do this Please Help me.


aditya kumar Patel

Total Post:250

Points:1782
Posted on    March-03-2016 10:50 PM

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

 


Don't want to miss updates? Please click the below button!

Follow MindStick