When you have a table with identity column, you don’t need to add values to that column and it is automatically populated based on the definition. However if you want to add some values explicitly, you need to use IDENTITY_INSERT ON option. But you need to understand that when you use that option, you need to explicitly type out all column names in the INSERT Statement.
Consider the following table and data
create table identity_test(id int identity(1,1),first_name varchar(100)) insert into identity_test (first_name ) select 'Madhivanan' union all select 'Nilesh' select * from identity_test
The result isid first_name
If you want to add a record with explicitly giving ID value, you can use
set identity_insert identity_test on insert into identity_test(id,first_name ) select 100,'Vignesh' set identity_insert identity_test off
You need to note that if you don’t specify the column names in the INSERT statement, you will get an error
set identity_insert identity_test on insert into identity_test select 100,'Vignesh' set identity_insert identity_test off
The error is
Msg 8101, Level 16, State 1, Line 4 An explicit value for the identity column in table 'identity_test' can only be specified when a column list is used and IDENTITY_INSERT is ON.
So be aware of this and always specify column list in the INSERT statement