SQL Server - How to Insert Values into Identity Column

Introduction: 

Here I will explain how to insert values in identity column in SQL Server. Identity columns are used to automatically assign new incremented value to identity column when new record inserted based on previous value in SQL Server.

Description:
  
In previous articles I explained Create Identity Column or Auto increment column in SQL Server, Reset identity column in SQL Server, SQL Query to get particular part of string, SQL Query to replace part of string, SQL Query to read xml file, SQL Query to get month wise, year wise data and many articles relating to SQL Server, jQuery, JavaScript. Now I will explain how to insert values into an identity column in SQL Server.

To insert value in identity column I will explain with one example for that first create one sample table like as shown below

CREATE TABLE UserDtls  (  UserId int PRIMARY KEY IDENTITY,  UserName varchar(120),  Qualification varchar(50)  )

Once we create UserDtls insert data like as shown below

INSERT INTO UserDtls(UserId,UserName,Qualification) VALUES(1,'Suresh','B.Tech')

Whenever we run above query we will get error message like as shown below

Msg 544, Level 16, State 1, Line 1 Cannot insert explicit value for identity column in table 'UserDtls' when IDENTITY_INSERT is set to OFF.

Based on above error message we can realize that identity columns won’t allow to insert new values when IDENTITY_INSERT is OFF .To solve this problem we need to set is ON for that we need to write the code like as shown below

SET IDENTITY_INSERT UserDtls ON INSERT INTO UserDtls(UserId,UserName,Qualification) VALUES(1,'Suresh','B.Tech') INSERT INTO UserDtls(UserId,UserName,Qualification) VALUES(2,'Rohini','MSC') SET IDENTITY_INSERT UserDtls OFF

Once we run above query our Output will be like this

--------------------------------------- (1 row(s) affected) (1 row(s) affected)

In this way we can insert values to identity columns in SQL Server.

If you enjoyed this post, please support the blog below. It's FREE! Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

Subscribe by RSS Subscribe by Email