Inserting auto increment values through Identity

Question

Post reply

Inserting auto increment values through Identity

shad.saleem41

Mr or Mrs. 500

Points: 549
More actions
October 4, 2013 at 12:25 am

#283448

Hi
I created a table
CREATE TABLE IdentityProducts(
productid int IDENTITY(1,1) NOT NULL,
productname nvarchar(40) NOT NULL,
categoryid int NOT NULL,
unitprice money NOT NULL)
I want to insert values into it with the belkow statement
insert into IdentityProducts(productname,categoryid,unitprice)
values
('shad',1,100)
when i insert the "productid" should get inserted automatically as it is defined with identity propert
but its not happening
even though i have made
SET IDENTITY_INSERT IdentityProducts ON
I am getting the following error
============
Msg 545, Level 16, State 1, Line 1
Explicit value must be specified for identity column in table 'IdentityProducts' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column.
==============
:crying:

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Pulivarthi Sasidhar SSCertifiable Points: 6706 More actions · Answer 1

Hi,

It should work...

Run the below statement and then insert values..

SET IDENTITY_INSERT IdentityProducts OFF

insert into IdentityProducts(productname,categoryid,unitprice)

values

('shad',1,100)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

shad.saleem41 (10/4/2013)
Hi
I created a table
CREATE TABLE IdentityProducts(
productid int IDENTITY(1,1) NOT NULL,
productname nvarchar(40) NOT NULL,
categoryid int NOT NULL,
unitprice money NOT NULL)
I want to insert values into it with the belkow statement
insert into IdentityProducts(productname,categoryid,unitprice)
values
('shad',1,100)
when i insert the "productid" should get inserted automatically as it is defined with identity propert
but its not happening
even though i have made
SET IDENTITY_INSERT IdentityProducts ON
I am getting the following error
============
Msg 545, Level 16, State 1, Line 1
Explicit value must be specified for identity column in table 'IdentityProducts' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column.
==============
:crying:

If you turn IDENTITY_INSERT on for a table, SQL Server is expecting you to provide the values for that column rather than having the values generated automatically. As stated above, turn IDENTITY_INSERT off and try your code again.

shad.saleem41 Mr or Mrs. 500 Points: 549 More actions · Answer 3

Thanks it worked

I re created table and renoved not null after identity property and inserted values it worked

CREATE TABLE IdentityProducts(

productid int IDENTITY(1,1),

productname nvarchar(40) NOT NULL,

categoryid int NOT NULL,

unitprice money NOT NULL)

insert into IdentityProducts(productname,categoryid,unitprice)

values

('shad',1,100)

this worked for me