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

Advertisements

2 Responses to “SET IDENTITY INSERT – how to insert value in IDENTITY Column in Microsoft SQL Server”

  1. Ritesh Shah Says:

    BTW, there is one more interesting fact about inserting values in IDENTITY column.create table SQLHub(id int identity(1,1))GOinsert into sqlhub default valuesGOselect * from SQLHubgo

  2. Ritesh Shah Says:

    BTW, there is one more interesting fact about inserting values in IDENTITY column.create table SQLHub(id int identity(1,1))GOinsert into sqlhub default valuesGOselect * from SQLHubgo


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: