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 ?

Improve this question
4
  • 3
    A similar question was already answered here: stackoverflow.com/questions/1334012/… Commented Jan 29, 2014 at 20:21
  • 1
    What's unclear about the error message? Commented Jan 29, 2014 at 20:23
  • @learner Turn IDENTITY_INSERT to ON before inserting. Commented Jan 29, 2014 at 20:25
  • 2
    Or don't insert explicit values into an IDENTITY column! Let SQL Server handle those auto-incrementing values! Commented Jan 29, 2014 at 20:25

2 Answers 2

Reset to default
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
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

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.
@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
1

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

SET IDENTITY_INSERT Tablename ON/OFF
Improve this answer

Comments