0

I want to be able to insert something into my table at a specific ID, so I turned IDENTITY_INSERT on for the table. However, if I just want the auto increment to handle the ID, this error appears:

"Explicit value must be specified for identity column in table 'TsiList' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column."

Is there a way to make the queries

INSERT INTO table (ID, something_else) VALUES (15, 'foo'); 

and

INSERT INTO table (something_else) VALUES ('foo'); 

work at the same time?

ADyson
  • 57,178
  • 14
  • 51
  • 63
Sven Engling
  • 39
  • 1
  • 7

1 Answers1

1

You can't do it without switching identity_insert on and off as required in between running each query.

Each version will only work when identity_insert is set to the relevant value within the session in which the query is being executed.

For example:

SET IDENTITY_INSERT TsiList ON;
INSERT INTO TsiList (ID, something_else) VALUES (15, 'foo'); 
SET IDENTITY_INSERT TsiList OFF;
INSERT INTO TsiList (something_else) VALUES ('foo');
ADyson
  • 57,178
  • 14
  • 51
  • 63
  • That's ... unexpectedly cumbersome. And I thought I was just missing a function. Guess I'll accept this. Thank you for the info :) – Sven Engling Jan 09 '19 at 11:13
  • 1
    Cumbersome? Not really: it's just the DBMS enforcing the rules you've given it. If you say you're going insert with explicit IDs (which is what you're telling it when you set identity_insert on), then that's what you have to do. It doesn't guess that you've decided not to bother and want to use the auto-increment, instead it thinks you've forgotten to supply the ID as promised, and refuses to carry on until you do. In its eyes it's protecting you from making an unintentional mistake. If you'd like an extra option to enable a fallback type behaviour instead, you'd have to talk to Microsoft :-) – ADyson Jan 09 '19 at 11:31