Menu
I have a table that has a forced auto increment column and this column is a very valuable ID that is retained through out the entire app. Sorry to say it was poor development on my part to have this be the auto incrementing column.
So, here is the problem. I have to insert into this table an ID for the column that has already been created and removed from the table. Kind of like resurrecting this ID and putting it back into the table.
So how can I do this programatically do this without turning the column increment off. Correct me if I am wrong, if I turn it off programatically, It will restart at 0 or 1 and I don't want that to happen...
692
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.
It could have multiple causes: Check if the auto_increment value on the table itself, has the next highest value. Mind that if you have transactions where you INSERT a row and rollback the transaction, that auto_increment value will be gone/skipped.
To get the next auto increment id in MySQL, we can use the function last_insert_id() from MySQL or auto_increment with SELECT. Creating a table, with “id” as auto-increment.
ALTER TABLE Inventory MODIFY COLUMN item_number INT AUTO_INCREMENT=50; After running this code, future item IDs will start at an item_number of 50 and increment by 1. To change the starting increment value and increment in SQL Server, set your non-default values during table creation.
If you are in Microsoft SQL Server, you can "turn off" the autoIncrementing feature by issuing the statement Set Identity_Insert [TableName] On, as in:
Set Identity_Insert [TableName] On -- -------------------------------------------- Insert TableName (pkCol, [OtherColumns]) Values(pkValue, [OtherValues]) -- ---- Don't forget to turn it back off ------ Set Identity_Insert [TableName] Off 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
