How to Insert Values to Identity Column in SQL Server?

Override auto-generated IDs when you need to insert specific values manually.

Columns defined as IDENTITY in SQL Server auto-generate their values, so by default you can't insert a specific number into them. But there are legitimate situations — migrating data, restoring specific IDs, or fixing gaps — where you need to insert an explicit value.

The Problem

INSERT INTO Employees (EmployeeID, Name) VALUES (105, 'Riya Sharma');
-- Error: Cannot insert explicit value for identity column
-- unless IDENTITY_INSERT is set to ON.

The Solution: SET IDENTITY_INSERT

SET IDENTITY_INSERT Employees ON;

INSERT INTO Employees (EmployeeID, Name)
VALUES (105, 'Riya Sharma');

SET IDENTITY_INSERT Employees OFF;

Important Rules to Remember

• You must explicitly list the column names in the INSERT statement
• Only one table per session can have IDENTITY_INSERT set to ON at a time
• Always turn it back OFF immediately after the insert to avoid conflicts

Common Scenarios

This technique is commonly used when migrating historical data between environments, restoring specific records after an accidental deletion, or keeping IDs consistent across a staging and production database.

Used carefully, SET IDENTITY_INSERT gives you full control over identity values without needing to redesign your schema.