How to insert value into primary key column in SQL Server?

Question

insert into Student 
values('1', 'joedio', 'newyark', GETDATE())

I get this error message when trying to run this SQL:

An explicit value for the identity column in table 'Student' can only be specified when a column list is used and IDENTITY_INSERT is ON.

Answer 1

If your table has Identity Column, then you have to mention all the other columns while inserting.

Just it is for sample:

INSERT INTO Student (ID, Name, State, Date) VALUES('1','joedio','newyark',GETDATE())

If the First column is Identity, then skip the value while inserting:

INSERT INTO Student (Name, State, Date) VALUES('joedio','newyark',GETDATE())

And If you want to Insert Values into an Identity Column in SQL Server

SET IDENTITY_INSERT IdentityTable ON

INSERT INTO Student (ID, Name, State, Date) VALUES('1','joedio','newyark',GETDATE())

SET IDENTITY_INSERT IdentityTable OFF

And also refer the link How to Insert Values into an Identity Column in SQL Server for More information.

Answer 2

If you wants to insert primary key by query even it is auto increment then you have to set IDENTITY_INSERT ON as below, it will allow you to insert unique value by query:

SET IDENTITY_INSERT [Tablename] ON;

Your query will be now:

SET IDENTITY_INSERT Student ON;

INSERT INTO Student VALUES('1','joedio','newyark',GETDATE());

SET IDENTITY_INSERT Student OFF;

If you wants that SQL manage it self then default it set to IDENTITY_INSERT OFF and Auto increment is set, means on every insert the new value is assigned to that PK column.

Better to execute with default setting SET IDENTITY_INSERT Student OFF because by manual inset possibility to insert duplicate value and it will throw an error.