Insert values into IDENTITY column of SQL server table / Disable IDENTITY Column in SQL server 2005

Identity columns automatically inserts value for each record inserted into the table . These columns are commonly used as primary keys.  Usually developers face problems while inserting records into these columns, It’s actually very easy to do.

Let me show this to you, with an example. Create a table using the query

CREATE TABLE TestTable (
	TestID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
	TestValue NVARCHAR(50) NOT NULL )

now try inserting records into this table using

INSERT TestTable(TestId, TestValue)
VALUES (1, 'record 1')

you will get an error stating

Cannot insert explicit value for identity column in table 'TestTable' 
when IDENTITY_INSERT is set to OFF.

The right way of doing inserting records , is by enabling the property “IDENTITY_INSERT” to ON.

SET IDENTITY_INSERT TestTable ON
INSERT TestTable(TestId, TestValue)
VALUES (1, 'record 1')
SET IDENTITY_INSERT TestTable OFF

By enabling the property, the record can be inserted into the table. There are some key points  that you need to remember, when it comes to identity inserts

• You must specify a value for the identity column, after enabling it on a table.
• It can only be enabled on one table at a time. If you try to do it for more than one table , you will receive an error message.
• The table should be owned by the user executing the query.
• you need to mention the fields in the insert statement. It might throw an error otherwise. that is, the highlighted part in the below query is mandatory for insert.

INSERT TestTable(TestId, TestValue)
VALUES (1, 'record 1')