Insert values into Identity column in sql server

How to insert values into identity column in sql server

If you create any table with identity column, SQLServer automatically assigns value to that column when a new row is inserted, If you want to explicitly insert a new record then we can insert by using
the below option

SET Identity_Insert table_name ON

Below is the example to demonstrate

-- Create Names table.
CREATE TABLE dbo.Names( Slno INT IDENTITY NOT NULL PRIMARY KEY, Name VARCHAR(40) NOT NULL )
GO
-- Inserting values into products table.
INSERT INTO dbo.Names(Name) VALUES ('Kalyan')
INSERT INTO dbo.Names(Name) VALUES ('Hari')
INSERT INTO dbo.Names(Name) VALUES ('Ravi')
INSERT INTO dbo.Names(Name) VALUES ('Vishnu')
INSERT INTO dbo.Names(Name) VALUES ('Satya')
GO

select * from Names  -- List all names with their slno's

delete from Names where Name='Hari'  -- Here we are removing record of slno =2

-- Now if you insert a new record the record number will be 6, Let see
Insert into Names values ('Sandeep')
select * from Names  -- Verify the slno for newly inserted record, It will be 6

-- If you want to fill the gap record then we will use the below commands, We need to switch on Identity_Insert = ON on table name

SET IDENTITY_INSERT dbo.Names ON

Insert into Names (slno, name )values (2, 'Hari')

SET IDENTITY_INSERT dbo.Names OFF


If you directly tried without IDENTITY_INSERT ON then you will encounter the below error message

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