SQL Cannot insert explicit value for identity column in table 'Table' when IDENTITY_INSERT is set to OFF

Question

Structure tables and result query on sqlfiddle I want use query: ``` INSERT INTO Price (id_price, id_firm, id_city, name) VALUES ('12002', '1429', '73041', 'АРЕНДА (ПРОКАТ) АВТОКРАНА Г/П 25Т'), ('12003', '1429', '73041', 'ПЛИТКА КЕРАМИЧЕСКАЯ ГРАНИТ (КЕРАМОГРАНИТ) АССОРТ.'), ('12004', '1429', '73041', 'РАБОТЫ ГРУЗОПОДЪЕМНЫЕ АВТОВЫШКА (ПОДЪЕМНИК)'), ('12005', '1429', '73041', 'РАБОТЫ ГРУЗОПОДЪЕМНЫЕ АВТОКРАНОМ Г/П 25Т'), ('12006', '1429', '73041', 'РАБОТЫ КОМПРЕССОРОМ ВСЕ ВИДЫ'), ('12007', '1429', '73041', 'РАБОТЫ ПОГРУЗОЧНО-РАЗГРУЗОЧНЫЕ АВТОКРАНОМ-МАНИПУЛЯТОРОМ ГРУЖУ-ВОЖУ НА БАЗЕ ГАЗ Г/П 4Т'), ('12008', '1429', '73041', 'РАБОТЫ СПЕЦТЕХНИКОЙ ВСЕ ВИДЫ'), ('12009', '1429', '73041', 'РАБОТЫ СТРОИТЕЛЬНЫЕ ВСЕ ВИДЫ'), ('120010', '1429', '73041', 'ФОРСУНКА Д/КАМАЗ ДВИГАТЕЛЬ КАММИНС (CUMMINS) АССОРТ.'); ``` But i get errors: ``` Cannot insert explicit value for identity column in table 'Price' when IDENTITY_INSERT is set to OFF.: INSERT INTO Price (id_price, id_firm, id_city, name) VALUES ('1200…

Accepted Answer

```
SET IDENTITY_INSERT Table_Name ON;
GO

/* Do your Inserts */

SET IDENTITY_INSERT Table_Name OFF;
GO
```

Note

Not a good practice, not advised at all. You may very well end up having duplicate values so let the identity column generate the values for you. if you want to be able to insert the values yourself then do not make it an identity column at all.

If you are explicitly inserting values in an Identity column, to make sure you never end up with duplicate values, you can reseed the identity column value after you have explicitly inserted values:

```
DBCC CHECKIDENT ('Table_Name', RESEED, 0); --<-- Reseed value to 0
GO

DBCC CHECKIDENT ('Table_Name', RESEED);    --<-- Reseed value to next available value
GO
```

SQL Cannot insert explicit value for identity column in table 'Table' when IDENTITY_INSERT is set to OFF

Solution

Problem

Related problems