SET IDENTITY INSERT – how to insert value in IDENTITY Column in Microsoft SQL Server

I want to share one of my experiences about IDENTITY column. When I was very small kid in SQL Server few years back and I used to do programming in PHP with MySQL back end. I have just started learning SQL Server at that time one thought pops up in my mind. There is no way to insert value in IDENTITY column (that’s what I was thinking at that time after reading IDENTITY column from book) than what happens to the table I have with just one column and that column is IDENTITY enable column?

I tried to search out the way and finally found it and got very happy at that time. I didn’t even think that I would write article on SQL Server and especially on this experience.

–CREATE DEMO TABLE

CREATE TABLE IdentityColumn

(

ID INT IDENTITY(1,1),

NAME VARCHAR(10)

)

GO

–TRYING TO INSERT VALUE

INSERT INTO IdentityColumn VALUES(1,‘Ritesh’)

GO

–above statement will show you below given error

–Msg 8101, Level 16, State 1, Line 1

–An explicit value for the identity column in table ‘IdentityOneColumn’ can only be specified

–when a column list is used and IDENTITY_INSERT is ON.

–SET IDENTITY_INSERT to ON so that we can enter value manually in IDENTITY column

SET IDENTITY_INSERT IdentityColumn ON

–INSERT record in IDENTITY column with column list in INSERT

INSERT INTO IdentityColumn(id,name) VALUES(1,‘Ritesh’)

–once you done, set value OFF to IDENTITY_INSERT

SET IDENTITY_INSERT IdentityColumn OFF

GO

–check the table

SELECT * FROM IdentityColumn

–again start inserting regularly

INSERT INTO IdentityColumn(name) VALUES(‘RAJAN’)

–AGAIN check the table

SELECT * FROM IdentityColumn

–one more time manual insert

SET IDENTITY_INSERT IdentityColumn ON

INSERT INTO IdentityColumn(id,name) VALUES(5,‘Alka’)

SET IDENTITY_INSERT IdentityColumn OFF

GO

–AGAIN check the table

SELECT * FROM IdentityColumn

–again start inserting regularly and check table

INSERT INTO IdentityColumn(name) VALUES(‘RAvi’)

SELECT * FROM IdentityColumn

After observing above behavior, you come to know that, if you insert big value manually in IDENTITY column, next regular value will follow you value.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah