0

I have parent and a child tables where child table contains reference of a foreign key to primary key in a parent table. If I try to insert a record via Microsoft SQL Server Management Studio into child table once there's a same record in a parent table - it acts as should.

The problem comes when trying to do the same in Visual Studio which returns an error:

An explicit value for the identity column in table 'child_table' can only be specified when a column list is used and IDENTITY_INSERT is ON.

All I was able to find that you enable it just temporary, is it possible to have it always on? Or maybe there's another solution? Thanks for ant explanation.

Ženia Bogdasic
  • 117
  • 1
  • 17
  • Does your child table have an identity PK that you are trying to insert a specific id into? – Paddy Feb 21 '15 at 21:53
  • 1
    Why on earth do you want an identity which will never be an identity? – Giorgi Nakeuri Feb 21 '15 at 21:54
  • when specifying the insert statement, leave the column that is the IDENTITY out of your insert list. In other words, don't try to programatically specify what the value should be. SQL Server will automatically calculate it. – Jeremy Feb 21 '15 at 21:59
  • @Jeremy This is what happens when I do it like that: The INSERT statement conflicted with the FOREIGN KEY constraint – Ženia Bogdasic Feb 21 '15 at 22:15
  • Did you check this post? It looks like your issue: http://stackoverflow.com/questions/24090388/adding-related-entities-without-using-navigation-properties/24090767#24090767 – Ako Feb 21 '15 at 22:46
  • 1
    post the schema of your tables. I have a feeling you are making a field an identity when it should not be. I fyou NEED to insert into this field, turn off the IDENTITY spec on it, then you can insert your own value. – Jeremy Feb 21 '15 at 22:53
  • Instead of `insert into tablename select * from...` try `insert into tablename select identity_col,col1,col2,.. from...` – Pரதீப் Feb 22 '15 at 02:17

0 Answers0