Add the value into an Identity Column in SQL Server

Varinder Sandhu SQL, SQL-Query, SQL-Scripts, SQL-Tips and Tricks, Varinder Sandhu

Identity columns are commonly used as primary keys in database tables.
These columns automatically assign a value for each new row inserted.
But sometime Identity columns missed the value, we want to insert missed value into the column. For that we need to enable IDENTITY_INSERT for the table.

USE mytempDB
GO

CREATE TABLE myTable
(
myIdentity INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
myValue NVARCHAR(30) NOT NULL
)
GO

INSERT myTable(myIdentity, myValue)
VALUES (5, ‘Varinder Sandhu’)

–Result = Error because IDENTITY is OFF

–Below enable IDENTITY_INSERT

SET IDENTITY_INSERT myTable ON

INSERT myTable(myIdentity, myValue)
VALUES (5, ‘Varinder Sandhu’)

SET IDENTITY_INSERT myTable OFF

Note:

Once we enabled IDENTITY_INSERT on a table you must specify a value for the identity column.

–If IDENTITY_INSERT is OFF then use below insert statement for above example

INSERT myTable(myValue)
VALUES (‘Varinder Sandhu’)