What is the syntax for adding a row to a table that has an auto-incremented column?

Question

I'm new to SQL and have a question about how to insert a row into a table that has an auto-incremented column. In my script I create the table

CREATE TABLE orgs ( O_Id int NOT NULL identity(1,1), org varchar (255) NOT NULL, PRIMARY KEY (O_Id) );

which is supposed to look like

                   orgs
-----------------------------------------------------
      O_Id           |            orgname
-----------------------------------------------------
        1            |          "StackOverflow"
-----------------------------------------------------
        2            |          "Madoff Investments"

However, I don't know how to insert rows into this table. W3Schools shows the syntax

INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);

but I'm not sure how to use that when one of the columns is auto-incremented. I've tried

INSERT INTO orgs (O_id, orgname) VALUES (DEFAULT, 'StackOverflow');

but get the error

DEFAULT or NULL are not allowed as explicit identity values.

I've also tried overriding,

INSERT INTO orgs (O_id, orgname) VALUES (1, 'StackOverflow');

but get the error

Cannot insert explicit value for identity column in table 'orgs' when IDENTITY_INSERT is set to OFF.

What should I be doing?

Simply omit the first column and pass the non-key values only. — Mr. Mascaro, May 08 '15 at 15:50
@Mr.Mascaro you should expand your comment into an answer to get the credit for it! — Mike P, May 08 '15 at 15:52
@FiredfromAmazon.com can you show your updated insert statement? — Mike P, May 08 '15 at 15:54

score 2 · Answer 1 · answered May 08 '15 at 16:00

insert orgs 
        (orgname) 
    values 
        ('StackOverflow')

That's the benefit of the IDENTITY designation; those values are incremented and assigned automatically for you.

Now, if you actually need to insert specific values (say you're seeding a template database with known data, or something), you can do this:

set @@IDENTITY_INSERT orgs ON
insert orgs 
        (O_id, orgname) 
    values 
        (1, 'StackOverflow')
set @@IDENTITY_INSERT orgs OFF

Setting @@IDENTITY_INSERT to ON for that table means to allow you to set specific values. Normally, you wouldn't do that.

score 0 · Answer 2 · answered May 08 '15 at 15:53

0

Sytax:
INSERT orgs
VALUES('nextOrg')

If the field is auto-inc you simply ignore it on INSERT commands.

answered May 08 '15 at 15:53

Mr. Mascaro

2,693
1
11
18

What is the syntax for adding a row to a table that has an auto-incremented column?

2 Answers2