CODEWITHSUNDEEP
sqlsql-server
Usman Hussain
Usman Hussain

Reputation: 1

How to Insert Row Without Auto incrementing a ID Column

I have a table that has a forced auto increment column. but some time i have to insert the value explicitly. For this purpose i set identity to on but it's not workin

Here is the code:

SET IDENTITY_INSERT [menu_code] on

INSERT INTO [dbo].[menu_code]
VALUES('', '', '', '', '',
       1  -- Auto Generated Identity column
        ,'', '')

Upvotes: 0

Views: 2477

Answers (1)

marc_s
marc_s

Reputation: 755451

The error message is crystal clear on the cause of the problem:

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

You need to explicitly define what the columns are that you're trying to insert values into!

SET IDENTITY_INSERT [menu_code] ON

INSERT INTO [dbo].[menu_code] (Col1, Col2, Col3, Col4, Col5, IdentityCol, Col6, Col7)
VALUES('', '', '', '', '',
       1  -- Auto Generated Identity column
        ,'', '')

SET IDENTITY_INSERT [menu_code] OFF

I would recommend to do this all the time anyway - if you omit the list of columns (both on INSERT and SELECT statements), you're setting yourself up for nasty surprises down the road. Yes, it's a tad more typing - once, when you create the query - but it'll save you countless hours of headaches and trying to find nasty bugs...... just define the list of columns you want to work with explicitly, all the time.

Update: if you're inserting those values from another table, I would HIGHLY recommend using explicit lists of column names in both the INSERT as well as the SELECT:

SET IDENTITY_INSERT [menu_code] ON

INSERT INTO [dbo].[menu_code] (Col1, Col2, Col3, Col4, Col5, IdentityCol, Col6, Col7)
   SELECT Col1, Col2, Col3, Col4, Col5, IdentityCol, Col6, Col7
   FROM DM_LUBRICANTS.dbo.menu_code 

SET IDENTITY_INSERT [menu_code] OFF

Upvotes: 3

Related Questions