7

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 
    ('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) АССОРТ.');

Tell me please why i get errors and how correct insert data ?

  • 3
    A similar question was already answered here: http://stackoverflow.com/questions/1334012/cannot-insert-explicit-value-for-identity-column-in-table-table-when-identity – Brian H Jan 29 '14 at 20:21
  • 1
    What's unclear about the error message? –  Jan 29 '14 at 20:23
  • @learner Turn `IDENTITY_INSERT` to `ON` before inserting. – user2989408 Jan 29 '14 at 20:25
  • 2
    Or don't insert explicit values into an `IDENTITY` column! Let SQL Server handle those auto-incrementing values! – marc_s Jan 29 '14 at 20:25

2 Answers2

23
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
M.Ali
  • 67,945
  • 13
  • 101
  • 127
  • 2
    I don't entirely agree with your note. There are times when it's handy to do this, for example in a ref data script that sets up an application's initial data (e.g. when it's first installed). Imagine your application enables users to be added through the frontend (so you want an identity column) but you want to start off with (say) an admin user so you have an initial script to set this up. You might need to use the ID of this user later in the script as a foreign key in another table, so you need to specify it explicitly. – Martin Wilson Jan 29 '14 at 20:58
  • 1
    @MartinWilson agreed. sometimes it is the only way to go but I have advised OP on `best practice`, exceptionally sometime you have no other choice. but in such a situation where I have to insert values into an identity column, I would make sure that I reseed the value to the next highest value after my inserts using DBCC RESEED command but I guess it will be too much details for a simple answer – M.Ali Jan 29 '14 at 21:02
1

If this is the error than: you can do either Truncate and reload the table or 

SET IDENTITY_INSERT Tablename ON/OFF
Walery Strauch
  • 6,792
  • 8
  • 50
  • 57
Maverick
  • 1,167
  • 1
  • 8
  • 16