CODEWITHSUNDEEP
sql-serversql-server-2005
divikdiya
divikdiya

Reputation: 363

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

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.

Upvotes: 30

Views: 137978

Answers (7)

Shushil Bohara
Shushil Bohara

Reputation: 5656

TRY THIS: If you really want to insert a value into the IDENTITY column, then you must enable this for the table first

SET IDENTITY_INSERT [Tablename] ON;
INSERT INTO [Tablename]..........
SET IDENTITY_INSERT [Tablename] OFF;

Otherwise, just get rid of the error by excluding the first value from the values

INSERT INTO Student --No need to add column definition if you are inserting rest of all
VALUES('joedio', 'newyark', GETDATE()) --Error Free

Upvotes: 7

DineshDB
DineshDB

Reputation: 6193

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.

Upvotes: 13

Vivektwr29
Vivektwr29

Reputation: 52

  1. In sql server when ID column is Auto Incremental and you are passing ID

IF NOT EXISTS (SELECT * FROM Student WHERE ID = 1) THEN

SET IDENTITY_INSERT Student ON;

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

SET IDENTITY_INSERT Student OFF;

END

2.In sql server when ID column is Auto Incremental and you are not passing ID

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

3.In My sql use can use

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

OR 

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

Upvotes: 1

divikdiya
divikdiya

Reputation: 363

SET IDENTITY_INSERT Student ON;

INSERT INTO Student (RegNo, Name, Address, CreatedTime) VALUES('2','calibio','newyark',GETDATE());

SET IDENTITY_INSERT Student OFF;

Upvotes: 0

Sandip - Frontend Developer
Sandip - Frontend Developer

Reputation: 14669

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.

Upvotes: 28

Farrukh Sarmad
Farrukh Sarmad

Reputation: 325

Try to clear out this concept by taking a look at it.

IDENTITY (Property)

And after mentioning the primary key as identity, you need not to pass an argument for its value. Its value will be auto incremented.

Upvotes: 4

usr_11
usr_11

Reputation: 574

IDENTITY_INSERT allows explicit values to be inserted into the identity column of a table. Use this query and set IDENTITY_INSERT on the table 'on'

SET IDENTITY_INSERT Student ON

Note: At any time, only one table in a session can have the IDENTITY_INSERT property set to ON.

Upvotes: 0

Related Questions