How to insert values to identity column in SQL Server.

Anonymous User 1840 03 Mar 2016

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

Ask by Anonymous User

Updated 03 Mar 2016

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

Markdown for AI

A clean, structured version of this page for AI assistants and LLMs.

Open .md

forum

How to insert values to identity column in SQL Server.

Can you answer this question?

1 Answers