[MS SQL]解決 Cannot insert explicit value for identity column in table ''YourTableName'' when IDENTITY_INSERT is set to OFF. Severity 16

問題

---

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



解決方法

---

當資料表的PRIMARY KEY 是設在有identity 的欄位,資料庫預設是不允許寫入的,因為標記為identity 的欄位=>資料庫會自行指派值
所以當想寫入資料到這個欄位時就會出現以下錯誤:
Cannot insert explicit value for identity column in table 'YourTableName' when IDENTITY_INSERT is set to OFF. Severity 16

解決方式:
在Insert資料前設

SET IDENTITY_INSERT [YourTableName] ON

如此一來資料庫在你寫入資料到設有identity 屬性的欄位時,就不會自動指派值了!
PS.記得INSERT完以後要設為OFF歐!!

另外,使用過的identity會以最後的值為主,若要指定開始值,可以使用:

 DBCC CHECKIDENT ( [YourTableName] , reseed,[start_number_you_want])