DataGinger.com

By default, SQL Server automatically assigns a value to the Identity Column for each new row inserted into the table. However, if desired, we can insert explicit values into the Identity column when IDENTITY_INSERT property is set to ON.

Let us take an example from AdventureWorks2012 database. Table [HumanResources].[Department] is having DepartmentID as Identity column.

Now, if we want to insert a value into the DepartmentID which is an Identity column, we can achieve this by:

--Turning ON the IDENTITY_INSERT property
SET IDENTITY_INSERT [HumanResources].[Department] ON
GO

--Now insert a sample row into table with DepartmentID = 1001
INSERT INTO [HumanResources].[Department] (DepartmentID, Name, GroupName, 
ModifiedDate) -- MUST to specify the Identity column name 
VALUES (1001, 'SureshRaavi', 'Operations', GetDate())
GO

--Don't forget to turn OFF the IDENTITY_INSERT
SET IDENTITY_INSERT [HumanResources].[Department] OFF
GO

Note:
If we try inserting values without turning ON the IDENTITY_INSERT, we will encounter the below error message:
Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in table ‘Department’ when IDENTITY_INSERT is set to OFF.

However, only one table in a session can have the IDENTITY_INSERT property set to ON at any time. And, user must own the table or have ALTER permission on the table in order to do this.

Read Full Post »