1
i want to enter record in DS_ITEM and the mistake you give me is "Cannot insert explicit value for identity column in table 'SCD_ITEM_CONTR' when IDENTITY_INSERT is set to OFF". How can I make this record?
insert into SCD_ITEM_CONTR (PK_ITEM_CONTR,DS_ITEM,PK_UNIDADE_FORNEC,PK_NAT_DESP,PK_CONTR) values (14,'Java, JavaScript - Contagem Detalhada',1,1,28)
being PK_UNIDADE_FORNEC,PK_NAT_DESP,PK_CONTR foreign keys
1
The column 'SCD_ITEM_CONTR' is an identity column, so you have to activate an identity insert in the table, and insert the value specifications in the table.
See if this solves:
set IDENTITY_INSERT SCD_ITEM_CONTR on
insert into IDENTITY_INSERT
(PK_ITEM_CONTR,
DS_ITEM,
PK_UNIDADE_FORNEC,
PK_NAT_DESP,
PK_CONTR)
values (14,
'Java, JavaScript - Contagem Detalhada',
1,
1,
28)
set IDENTITY_INSERT SCD_ITEM_CONTR off
It’s not very recommended to do this, so I recommend doing it in a test environment first. But do not forget after running SET IDENTITY_INSERT SCD_ITEM_CONTR OFF
But that’s what would solve the problem with error!
This option can only be enabled for a single table across the database - so if you leave it enabled for one table, you can never use it for another table. Also: it is not an option that should be left on - by default you should let SQL Server handle identity values - intended only as a measure of last resort for very specific cases - it is not a general use option to be enabled or disabled.
Matthew Venancio, could validate and mark the answer as correct?
Browser other questions tagged sql database sql-server
You are not signed in. Login or sign up in order to post.
If your PK_ITEM_CONTR primary key is of type Identity why do you provide a value for it? It would not be more practical not to inform such column in INSERT and let the system itself do the control?
– anonimo
That’s right, I had it all worked out, but thanks!
– Mateus Venancio