Inserts and the Identity field

[Thad]

I am trying to run an insert statement against a table, and I get an
error: "Can not insert explicit value for identity column in table
"table name" when Identity_Insert is set to off."

Now, in the set up of the table, when I populate it, I set the Column
in the columns collection to be an identity field and I set it to true
and all that.

This message seems to imply that I need to set something on the SQL
Server side, through the database, but I'm thinking I shouldn't have
to do that.

How do I get around this? Can I just set some property through the
ADO.Net? If so, what am I missing? I looked through about four pages
of old posts and didn't see anything similiar, but I'm sure this has
come up, and I apologize if I'm asking somethign that's been addressed
already.

Thanks,

Thad.

 

[David Sceppa]

Thad,

You need to change some server-side settings. By default, SQL Server
will not let you include a value for the auto-increment column in your
INSERT query. If you want to allow for this, you'll need to call SET
IDENTITY_INSERT. See SQL Server Books Online for more information.

I hope this information proves helpful.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2004 Microsoft Corporation. All rights reserved.

 

[Joe Fallon]

You are making a basic mistake.
When you insert a record to a table that uses an Identity, you omit that
field from the Insert statement altogether.
The DB will automatically generate the key value for you.

The only time you need to be concerned with inserting the key yourself if if
you are transferring existing data from a table in one DB to a simialr table
in another DB and you need the key to stay the same in both. (e.g. like when
you upgrade from Version 1 to Version 2 - your data conversion script needs
to extract data from the then V1 table and add it to the V2 table. In that
case you need to wrap your Insert statement with Identity On and Off
statements.)

 

[Thad]

Thad,

You need to change some server-side settings. By default, SQL Server
will not let you include a value for the auto-increment column in your
INSERT query. If you want to allow for this, you'll need to call SET
IDENTITY_INSERT. See SQL Server Books Online for more information.

I hope this information proves helpful.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2004 Microsoft Corporation. All rights reserved.

Now, I did the thing that the other person mentioned, that is, taking
the ID field out all together and that worked. Is this a setting I
should be setting anyway?

 

[Thad]

You are making a basic mistake.
When you insert a record to a table that uses an Identity, you omit that
field from the Insert statement altogether.
The DB will automatically generate the key value for you.

The only time you need to be concerned with inserting the key yourself if if
you are transferring existing data from a table in one DB to a simialr table
in another DB and you need the key to stay the same in both. (e.g. like when
you upgrade from Version 1 to Version 2 - your data conversion script needs
to extract data from the then V1 table and add it to the V2 table. In that
case you need to wrap your Insert statement with Identity On and Off
statements.)

I tried that and it worked, thanks.

 

[David Sceppa]

Thad,

Joe makes a very good point. Most developers and DBAs using identity
columns in their database are asking the database to generate their new
values. Trying to include the new values in the INSERT query runs counter
to this approach. Some developers may still want to push new values to the
database in some situations, which is why SQL Server makes the
IDENTITY_INSERT option available.

In your case, it sounds like you should either let the server generate
the new values or not have that column marked as an identity column.

I hope this information proves helpful.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2004 Microsoft Corporation. All rights reserved.