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

Facts about IDENTITY in SQL Server 2005

IDENTITY is a property and it can be used with Table object in Microsoft SQL Server. Basically it uses to maintain unique auto increment number for table.

Syntax of IDENTITY is:

IDENTITY [(seed,increment)]

SEED is a numeric value with which you want to load your first row in the table.

INCREMENT is a numeric value to identify the gap between two values of Identity column.

I have used IDENTITY column in my many past article but let us see this practically here once again.

NOTE: You must not insert value in identity column, SQL Server automatically find the value for it and INSERT it.

–CREATE demo table

CREATE TABLE IdentityCheck

(

ID INT IDENTITY(2,2),

Name VARCHAR(10)

)

–insert record in table with Identity

INSERT INTO IdentityCheck VALUES(‘Ritesh’)

INSERT INTO IdentityCheck VALUES(‘Rajan’)

–check what have been inserted

SELECT * FROM IdentityCheck

In IdentityCheck table we have set seed as 2, it means first row in our table will get value 2 in ID column and we have set increment as 2 so it means that every new row will add 2 in the previous row by this fact you can assume that our second row will insert value 4 in ID column.

If you delete rows from table and then will start inserting rows in table again, it won’t start ID value from 2, in our case, it will start from 6 because if you delete records, it will not reset your IDENTITY but yes if you TRUNCATE the table, IDENTITY will reset.

–check whether identity reset by deleting records.

DELETE FROM IdentityCheck

–let us insert records again.

INSERT INTO IdentityCheck VALUES(‘Ritesh’)

INSERT INTO IdentityCheck VALUES(‘Rajan’)

–let us check what value we got this time in ID column

–those will be 6 and 8

SELECT * FROM IdentityCheck

–let us now truncate table which will reset identity

TRUNCATE TABLE IdentityCheck

–let us insert records again.

INSERT INTO IdentityCheck VALUES(‘Ritesh’)

INSERT INTO IdentityCheck VALUES(‘Rajan’)

–let us check what value we got this time in ID column

–those will be 2 and 4

SELECT * FROM IdentityCheck

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