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]
[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 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.
0 comments:
Post a Comment