Inserting to table having issue - Explicit value must be specified for identity column in table

Question

I'm getting ready to release a stored procedure that gets info from other tables, does a pre-check, then inserts the good data into a (new) table. I'm not used to working with keys and new tables as much, and my insert into this new table I'm creating is having this error message having to do with the insert/key:

Msg 545, Level 16, State 1, Line 131
Explicit value must be specified for identity column in table 'T_1321_PNAnnotationCommitReport' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column.

BEGIN
...
    BEGIN
    IF NOT EXISTS (SELECT * FROM sys.tables where name = N'T_1321_PNAnnotationCommitReport')
        BEGIN
            CREATE TABLE T_1321_PNAnnotationCommitReport (
                 [id] [INT] IDENTITY(1,1) PRIMARY KEY NOT NULL,  --key
                 [progressnote_id] [INT] NOT NULL,
                 [form_id] [INT] NOT NULL,
                 [question_id] [INT],
                 [question_value] [VARCHAR](max),
                 [associatedconcept_id] [INT],
                 [crte_date] [DATETIME] DEFAULT CURRENT_TIMESTAMP,
                 [create_date] [DATETIME] --SCHED_RPT_DATE
            );
            print 'test';
            
        END
    END --if not exists main table
            SET IDENTITY_INSERT T_1321_PNAnnotationCommitReport ON;
...
    INSERT INTO dbo.T_1321_PNAnnotationCommitReport--(progressnote_id,form_id,question_id,question_value,associatedconcept_id,crte_date, create_date)  **I tried with and without this commented out part and it's the same.
        SELECT  progressnote_id,
                a.form_id,
                question_id,
                questionvalue,
                fq.concept_id,
                getdate(),
                a.create_date
        FROM (
            SELECT  form_id,
                    progressnote_id,
                    R.Q.value('@id', 'varchar(max)') AS questionid,
                    R.Q.value('@value', 'varchar(max)') AS questionvalue,
                    create_date
            FROM
                    @tableNotes t
            OUTER APPLY t.form_questions.nodes('/RESULT/QUESTIONS/QUESTION') AS R(Q)
            WHERE ISNUMERIC(R.Q.value('@id', 'varchar(max)')) <> 0
            ) a
        INNER JOIN [CKOLTP_DEV]..FORM_QUESTION fq ON
                   fq.form_id = a.form_id AND
                   fq.question_id  = a.questionid
                   --select * from T_1321_PNAnnotationCommitReport
                   SET IDENTITY_INSERT T_1321_PNAnnotationCommitReport OFF;
END

Any ideas?

I looked at some comparable inserts we do at work, insert into select and error message, and insert key auto-incremented, and I think I'm doing what they do. Does anyone else see my mistake? Thanks a lot.

The error is *literally* telling you the problem. You turn change the `IDENTITY_INSERT` property to `ON` for the table `T_1321_PNAnnotationCommitReport` and then omit the column `id` in your `INSERT`. If you have enabled `IDENTITY_INSERT` you **need** to supply a value to that `IDENTITY`, just like the error says. — Thom A, Sep 04 '20 at 15:27
I did that. I set Identity insert to on. Can you give an example of what's missing? I want it to auto-fill the key id's. — Michele, Sep 04 '20 at 15:30
You aren't inserting in the column, @Michele... The first column you declared appears to be `progressnote_id` in your `INSERT`. the `progressnote_id` column is *not* the `id` column. — Thom A, Sep 04 '20 at 15:34
Sounds like you're trying to insert a NULL value into your identity column. Run the SELECT part of your statement. Are there any NULL values for the identity? — critical_error, Sep 04 '20 at 15:35
I want the key column to start with 1 and self-populate. How do I do that? Can you give an example for that? Like an answer for me to accept? — Michele, Sep 04 '20 at 15:36
remove the `SET IDENTITY_INSERT T_1321_PNAnnotationCommitReport ON;` — Wouter, Sep 04 '20 at 15:39

score 2 · Accepted Answer · answered Sep 04 '20 at 15:37

To repeat my comment under the question:

The error is literally telling you the problem. You turn change the IDENTITY_INSERT property to ON for the table T_1321_PNAnnotationCommitReport and then omit the column id in your INSERT. If you have enabled IDENTITY_INSERT you need to supply a value to that IDENTITY, just like the error says.

We can easily replicate this problem with the following batches:

CREATE TABLE dbo.MyTable (ID int IDENTITY(1,1),
                          SomeValue varchar(20));
GO

SET IDENTITY_INSERT dbo.MyTable ON;
--fails
INSERT INTO dbo.MyTable (SomeValue)
VALUES('abc');
GO

If you want the IDENTITY value to be autogenerated, then leave IDENTITY_INSERT set to OFF and omit the column from the INSERT (like above):

SET IDENTITY_INSERT dbo.MyTable OFF; --Shouldn't be needed normally, but we manually changed it before
--works, as IDENTITY_INSERT IS OFF

INSERT INTO dbo.MyTable (SomeValue)
VALUES('abc');

If you do specifically want to define the value for the IDENTITY, then you need to both set IDENTITY_INSERT to ON and provide a value in the INSERT statement:

SET IDENTITY_INSERT dbo.MyTable ON;
--works
INSERT INTO dbo.MyTable (ID,SomeValue)
VALUES(10,'def');
GO

SELECT *
FROM dbo.MyTable;

IDENTITY_INSERT doesn't mean "Get the RDBMS to 'insert' the value" it means that you want to want to tell the RDBMS what value to INSERT. This is covered in the opening sentence of the documentation SET IDENTITY_INSERT (Transact-SQL):

Allows explicit values to be inserted into the identity column of a table.

(Emphasis mine)

Inserting to table having issue - Explicit value must be specified for identity column in table

1 Answers1