SET IDENTITY_INSERT

Allows explicit values to be inserted into the identity column of a table.

Syntax:
SET IDENTITY_INSERT [ database_name . [ schema_name ] . ] table { ON | OFF }

Example:
CREATE TABLE dbo.Tool(
ID INT IDENTITY NOT NULL PRIMARY KEY,
Name VARCHAR(40) NOT NULL
)
GO
-- Inserting values into products table.
INSERT INTO dbo.Tool(Name) VALUES ('Screwdriver')
INSERT INTO dbo.Tool(Name) VALUES ('Hammer')
INSERT INTO dbo.Tool(Name) VALUES ('Saw')
INSERT INTO dbo.Tool(Name) VALUES ('Shovel')
GO

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

SELECT * FROM dbo.Tool
GO

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

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

SELECT * FROM dbo.Tool
GO

--Try to insert name with out ID
--should return a warning.
INSERT INTO dbo.Tool(Name) VALUES ('Shovel')

-- SET IDENTITY_INSERT to OFF
SET IDENTITY_INSERT dbo.Tool OFF

--Try to insert name with out ID
INSERT INTO dbo.Tool(Name) VALUES ('Shovel')

SELECT * FROM dbo.Tool
GO

--Insert with 10 as id
--should return a warning.
INSERT INTO dbo.Tool (ID, Name) VALUES (10, 'Garden shovel')

--If you need to have gap between id then set ON
SET IDENTITY_INSERT dbo.Tool ON

--Insert with 10 as id
INSERT INTO dbo.Tool (ID, Name) VALUES (10, 'Garden shovel')

SELECT * FROM dbo.Tool
GO
--should return a warning.
INSERT INTO dbo.Tool (ID, Name) VALUES ('New Garden shovel')

SET IDENTITY_INSERT dbo.Tool OFF

--Insert and continue identity value from 10
INSERT INTO dbo.Tool (Name) VALUES ('New Garden shovel')

-- Drop products table.
DROP TABLE dbo.Tool
GO