How to Insert Identity Column in SQL Server?

IDENTITY property is used with the CREATE TABLE and ALTER TABLE Transact-SQL statements. it exposes the row identity property of a column. it will generate the number to maintain the unique records.

For Example, assume we have a Employee table

CREATE TABLE [dbo].[Employee](

            [EmpID] [int] IDENTITY(1,1) NOT NULL,

            [EmpName] [varchar](50) NOT NULL

) ON [PRIMARY]

When we insert the values of Empname then EmpID will be generated as a sequence number, as defined in the table schema. Assume we are inserting the following records,

INSERT INTO [dbo].[Employee]([EmpName]) VALUES ('BABU')

INSERT INTO [dbo].[Employee]([EmpName]) VALUES ('RAMESH')

INSERT INTO [dbo].[Employee]([EmpName]) VALUES ('MANI')

INSERT INTO [dbo].[Employee]([EmpName]) VALUES ('VIJAY')

INSERT INTO [dbo].[Employee]([EmpName]) VALUES ('VINODH')

INSERT INTO [dbo].[Employee]([EmpName]) VALUES ('KUMAR')

INSERT INTO [dbo].[Employee]([EmpName]) VALUES ('VARATHA')

INSERT INTO [dbo].[Employee]([EmpName]) VALUES ('JOTHI')

Now if you check the values of the table, It will be display with identity values as shown in exhibit

SELECT * FROM Employee

This identity is always generated by the system. We can not insert this value directly using INSERT statement.

If you insert directly, it will thru the error like

INSERT INTO dbo.[Employee](EmpID,EmpName) VALUES(1, 'BABU')

Msg 544, Level 16, State 1, Line 1

Cannot insert explicit value for identity column in table 'Employee' when IDENTITY_INSERT is set to OFF.

if you want insert manually or insert the identity value from other table, then we need to Turn off the table property IDENTITY_INSERT. We can do this, as given in the example.

SET IDENTITY_INSERT [Employee] ON

INSERT INTO dbo.[Employee](EmpID,EmpName) VALUES(10, 'VENKAT')

SET IDENTITY_INSERT dbo.[Employee] OFF

This will be helpful, when you delete and insert some record in the middle of the sequence of identity.
This is not recommandable, becoz If you insert the Existing sequence, it will accept. You can run the last script many times. So handle with careful.