How To Insert Values Into An Identity Column In SQL Server

How To Insert Values Into An Identity Column In SQL Server

In this tutorial i will explain you How To Insert Values Into An Identity Column In SQL Server.Identity columns are mostly used as primary keys in tables.These columns automatically assign a value for each new row.But if you want to insert your own value into the column then what?

Script:

---Create Table With a Column As Identity Insert---

create table emp_Temp
(
empid int Identity(1,1),
firstname char(20),
lastname char(20),
)

---Insert Value In Table ( It Will throw Error as Identity Insert is Not Allowed)---
Insert Into emp_Temp(empid,firstname,lastname) values(1,'Jai','Singh')

---Now Set Identity Insert On---
SET IDENTITY_INSERT emp_temp ON

---Now Insert Value In Table---
Insert Into emp_Temp(empid,firstname,lastname) values(2,'Jai','Singh')

---Now Set Identity Insert OFF---
SET IDENTITY_INSERT emp_temp OFF

Here are some key points about IDENTITY_INSERT:

1. It can only be enabled on one table at a time.

2. When it is enabled on a table you must specify a value for the identity column.

3.The user issuing the statement must own the object, be a (sysadmin role, dbo or db_ddladmin) role in order to run the command.