How to insert value into identity column SQL Server 2008

Sometimes in SQL Server Database table, we need to insert a new record with explicit value in identity column. at that time we face an error  with error Message: An explicit value for the identity column in table 'TABLE NAME'  can only be specified when a column list is used and IDENTITY_INSERT is ON. Here I am explaining with example how to resolve this issue.

Example of Identity Insert

Suppose there is a table CodeImagine_TblEmployee having below schema.

CREATE TABLE [dbo].[CodeImagine_TblEmployee](
                      [EmpCode] [int] IDENTITY(1,1) NOT NULL,
                      [Name] [varchar](100) NOT NULL,
                      [Mobile] [varchar](10) NOT NULL,
                      [Address] [nvarchar](200) NULL,  
    CONSTRAINT [PK_CodeImagine_TblEmployee] PRIMARY KEY CLUSTERED  ( [EmpCode] ASC
  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

I have inserted some records like-

INSERT INTO dbo.CodeImagine_TblEmployee

SELECT 'Ajit Singh','9012345678','Noida'

UNION 

SELECT 'Saurav Sarkar','9123456780','New Delhi'

UNION 

SELECT 'Virendra Maurya','9234567891','Kanpur'

UNION 

SELECT 'Alok Singh','9345678912','Faridabad'
...
...
...

Now I want to insert my own details with EmpCode=1000 and I tried with below DB script 

INSERT INTO dbo.CodeImagine_TblEmployee

SELECT  1000,'Pavan Bhardwaj','9911073055','New Delhi'

I am getting this error message

Msg 8101, Level 16, State 1, Line 2

An explicit value for the identity column in table 'dbo.CodeImagine_TblEmployee' can only be specified when a column list is used and IDENTITY_INSERT is ON.

but even after I ran

SET IDENTITY_INSERT dbo.CodeImagine_TblEmployee ON

INSERT INTO dbo.CodeImagine_TblEmployee

SELECT  1000,'Pavan Bhardwaj','9911073055','New Delhi'

I am still getting the same error message

Msg 8101, Level 16, State 1, Line 3

An explicit value for the identity column in table 'dbo.CodeImagine_TblEmployee' can only be specified when a column list is used and IDENTITY_INSERT is ON.

Solution: 

Have a look onto the best,simple & easiest way-

SET IDENTITY_INSERT dbo.TblEmployee ON

You have to make a column list for your INSERT statement:

SET IDENTITY_INSERT tableA ON

--You have to make a column list for your INSERT statement:

INSERT INTO dbo.CodeImagine_TblEmployee([EmpCode], [Name], [Mobile], [Address]) 

SELECT  1000,'Pavan Bhardwaj','9911073055','New Delhi'

SET IDENTITY_INSERT dbo.CodeImagine_TblEmployee OFF

--Now my record has been inseted with EmpCode 1000.

SELECT [EmpCode],[Name],[Mobile],[Address]

  FROM [aiccpo].[dbo].[CodeImagine_TblEmployee]

EmpCode Name                 Mobile             Address
1                Ajit Singh         9012345678     Noida
2                Alok Singh         9345678912     Faridabad
3                Saurav Sarkar 9123456780     New Delhi
4                Virendra Maurya 9234567891     Kanpur

1000        Pavan Bhardwaj 9911073055     New Delhi

Now let us check another example of Insert Identity ON

Ques: How to copy Data of One table to Other Table with Identity Column values

Solution:

SET IDENTITY_INSERT tableA ON

INSERT Into tableA ([id], [c2], [c3], [c4], [c5] ) 

SELECT [id], [c2], [c3], [c4], [c5] FROM tableB

--not like "INSERT Into tableA SELECT ........"

SET IDENTITY_INSERT tableA OFF

Author

I hope, you have enjoyed this article. I would like to have feedback from my blog readers. Your valuable feedback, question, or comments about this article are always welcome.