vendredi 8 mai 2015

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

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?

Aucun commentaire:

Enregistrer un commentaire