How to insert the values manually for the Identity Column ?

What is Identity Column :

•  For Identity Column, SQL Server will provide the values automatically at the time of inserting each record in the table.

Example :

-- Creating the table

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

-- To insert value to the identity column

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

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


SET IDENTITY_INSERT TestTable ON       -- Disable the Identity to Insert the values manually

INSERT TestTable(TestId, TestValue) VALUES (1, 'record 1')       -- Insert the values for the identity column

select * from TestTable

SET IDENTITY_INSERT TestTable OFF    -- Enable the Identity to Insert the values by the Server automatically