How to insert explicit values into Identity column

Problem: How to insert explicit values into Identity column in SQL SERVER.
Solution: Using SET IDENTITY_INSERT ON, we can insert explicit values into Identity column.

USE AdventureWorks2008R2;
GO
-- Create Student table.
CREATE TABLE dbo.Student(
   ID INT IDENTITY NOT NULL PRIMARY KEY,
   Name VARCHAR(40) NOT NULL
)
GO
-- Inserting values into products table.
INSERT INTO dbo.Student(Name) VALUES ('Raju')
INSERT INTO dbo.Student(Name) VALUES ('Subbu')
INSERT INTO dbo.Student(Name) VALUES ('Pandu')
INSERT INTO dbo.Student(Name) VALUES ('Prasad')
GO

-- Create a gap in the identity values.
DELETE dbo.Student
WHERE Name = 'Pandu'
GO

SELECT *
FROM dbo.Student
GO

-- Try to insert an explicit ID value of 3;
-- should return a warning.
INSERT INTO dbo.Student(ID, Name) VALUES (3, 'Satya')
GO
-- SET IDENTITY_INSERT to ON.
SET IDENTITY_INSERT dbo.Student ON
GO

-- Try to insert an explicit ID value of 3.
INSERT INTO dbo.Student(ID, Name) VALUES (3, 'Satya')
GO

SELECT *
FROM dbo.Student
GO
-- Drop products table.
DROP TABLE dbo.Student
GO

Reference: MSDN