Thursday, April 18, 2013

IDENTITY_INSERT in SQL Server

Posted by Rahul Kharde at 4:32 AM
This command is to enable the users to set their own value for IDENTITY Column in case they want to.  Using the setting of SET IDENTITY_INSERT is OFF it is possible to insert our own value into the IDENTITY Column.

Syntax as follows

SET IDENTITY_INSERT tablename ON
SET IDENTITY_INSERT tablename OFF

Let consider the below example

Create table Student with an identity column

CREATE TABLE [dbo].[Student]
(
    [StudentID]       INT NOT NULL IDENTITY(1, 1),
    [StudentName]  VARCHAR(100) NOT NULL
)

Insert record into the table 

INSERT INTO Student(StudentName) VALUES ('Name 1')
INSERT INTO Student(StudentName) VALUES ('Name 2')
INSERT INTO Student(StudentName) VALUES ('Name 3')
INSERT INTO Student(StudentName) VALUES ('Name 4')
INSERT INTO Student(StudentName) VALUES ('Name 5')

Let delete the record no 3 and 4

DELETE FROM Student WHERE StudentID=3
DELETE FROM Student WHERE StudentID=4

we try to insert record at no 3 position

INSERT INTO Student(StudentID,StudentName) VALUES (3,'New Name 3')

it shown error

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

First Set IDENTITY_INSERT as ON and insert the record with Identity column. It will allowed to insert the Identity column value

SET IDENTITY_INSERT Student ON

INSERT INTO Student(StudentID,StudentName) VALUES (3,'New Name 3')
INSERT INTO Student(StudentID,StudentName) VALUES (4,'New Name 4')

SET IDENTITY_INSERT Student OFF

SELECT * FROM Student






If you enjoyed this post and wish to be informed whenever a new post is published, then make sure you subscribe to my regular Email Updates. Subscribe Now!


Kindly Bookmark and Share it:

YOUR ADSENSE CODE GOES HERE

1 comments:

Naren on June 7, 2013 at 12:22 AM said...

Nice article .. keep posting :-)


Have any question? Feel Free To Post Below:

 

Popular Posts

Recent Comments

© 2011. All Rights Reserved | Help to understand .Net | Template by Blogger Widgets

Home | About | Top