Menu
Is it possible to insert specific value in identity field in a SQL table?
I deleted some rows and imported them again, but I need to insert some exceptions with exact IDs.
542
An explicit value for the identity column in table 'Students' can only be specified when a column list is used and IDENTITY_INSERT is ON. In simple words, the error says that since the flag IDENTITY_INSERT is off for the Id column, we cannot manually insert any values.
The system generates an IDENTITY column value when the keyword DEFAULT is used as the insert_clause for the IDENTITY column.
In any case, you cannot modify an identity column, so you have to delete the row and re-add with new identity. You cannot remove the identity property from the column (you would have to remove to column)
You can turn on/off inserting identity values with SET IDENTITY_INSERT On/Off:
To enable your custom values:
SET IDENTITY_INSERT TableName ON
To enable auto-values:
SET IDENTITY_INSERT TableName OFF
Note that you have to list the (non-nullable) columns explicitly if you want to insert identity values, like here:
INSERT INTO TableName (ID, Text, OtherColumns...) Values (99, 'foo', ...)
You can't omit the column-list by using this syntax:
INSERT INTO TableName Values (99,'foo')
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
