Search code examples
sqlsql-serveridentity-insert

Can you make inserting by Id optional?

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?

Solution

  • 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');