Sunday, 24 March 2013

IDENTITY_INSERT

Cannot insert explicit value for identity column in table ‘tblTestTable’ when IDENTITY_INSERT is set to OFF.

Suppose you have a table in MS Sql with two Columns.
CREATE TABLE [dbo].[tblTest](
[Test_Id] [int] IDENTITY(1,1) NOT NULL,
[Test_Name] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK_tblTest] PRIMARY KEY CLUSTERED
(
[Test_Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
Now you are inserting the three values in this table:
Insert Into tblTest(Test_Name) values(“Test1″)
Insert Into tblTest(Test_Name) values(“Test2″)
Insert Into tblTest(Test_Name) values(“Test3″)
So your table will look like as follows:
1 Test1
2 Test2
3 Test3
Now you are deleting second row of the table using the following syntax:
DELETE From tblTest where Test_Id=2
Now you table will looks like the following:
1 Test1
3 Test3
Now you are going to insert a row with the following query:
Insert Into tblTest(Test_Id,Test_Name) values(2, “TestTemp2″)
when you execute this query, you will face the following error:
This error is coming when we have a Identity Specification is ‘Yes’ and IsIdentity is also ‘Yes’, Identity Increament is set to(1/2/…).
So you are unable to insert this type of row, because sql know that the Test_Id is the Identity_Column and so you cannot insert the value which already inserted. So to resolve this error, you have to set the Column_Identity ON by using the following syntax.
SET IDENTITY_INSERT tblOrderItemStatus ON
Then try to insert the row using the above same query, which was as following:
Insert Into tblTest(Test_Id,Test_Name) values(2, “TestTemp2″)
Now you need to reset the Column_Identity OFF. You can do it just by the following syntax:
SET IDENTITY_INSERT tblOrderItemStatus OFF
Is it resolve your problem. Howdy buddy?….

0 comments:

Post a Comment

Sample Text

Muthukumar. Powered by Blogger.

About Me

My photo
Hi i am Muthu kumar,software engineer working on PL/SQL,ASP.Net,VB.Net,C#.Net,SQL Server.