Insert into Identity Column in Sql Server

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

USE AdventureWorks2012;

GO

-- Create tbltestIdentityInsert table.

CREATE TABLE dbo.tbltestIdentityInsert(

   ID INT IDENTITY NOT NULL PRIMARY KEY, 

   Name VARCHAR(40) NOT NULL

)

GO

-- Inserting values into tbltestIdentityInsert table.

INSERT INTO dbo.tbltestIdentityInsert(Name) VALUES ('Janaka')

INSERT INTO dbo.tbltestIdentityInsert(Name) VALUES ('Lahiru')

INSERT INTO dbo.tbltestIdentityInsert(Name) VALUES ('Iroshan')

INSERT INTO dbo.tbltestIdentityInsert(Name) VALUES ('Gihan')

GO

SET IDENTITY_INSERT dbo.tbltestIdentityInsert ON

INSERT INTO dbo.tbltestIdentityInsert(ID,Name) VALUES (10,'Chamara')

SET IDENTITY_INSERT dbo.tbltestIdentityInsert OFF

SET IDENTITY_INSERT dbo.Tool ON

Once you have set the identity insert on you can insert explicit values to the identity column.

SET IDENTITY_INSERT dbo.tbltestIdentityInsert OFF

Turn off the identity insert once the insertion is done.