时间戳

首页 > 解决方案 > 插入有问题的表 - 必须为表中的标识列指定显式值

sql-server - 插入有问题的表 - 必须为表中的标识列指定显式值

问题描述

我正准备发布一个存储过程，该过程从其他表中获取信息，进行预检查，然后将好的数据插入（新）表中。我不习惯使用键和新表，并且我插入到我正在创建的这个新表中时出现了与插入/键有关的错误消息：

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

有任何想法吗？

我查看了我们在工作中所做的一些类似插入，插入到 select和error message中，以及insert key auto-incremented，我想我正在做他们所做的事情。有没有人看到我的错误？非常感谢。

标签： sql-serverselectkeysql-insert

解决方案

重复我在问题下的评论：

该错误实际上是在告诉您问题所在。您将表的IDENTITY_INSERT属性更改为，然后在. 如果您已启用，则需要为 that 提供一个值，就像错误所说的那样。ONT_1321_PNAnnotationCommitReportINSERTIDENTITY_INSERTIDENTITY

我们可以通过以下批次轻松复制此问题：

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

如果您希望IDENTITY自动生成该值，则保留IDENTITY_INSERT设置OFF并省略列INSERT（如上）：

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

如果您确实想为 定义值IDENTITY，那么您需要在语句中IDENTITY_INSERT设置ON 并提供一个值：INSERT

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

SELECT *
FROM dbo.MyTable;

IDENTITY_INSERT 并不意味着“让 RDBMS '插入'值”它意味着你想告诉 RDBMS 什么值INSERT。这在文档SET IDENTITY_INSERT (Transact-SQL)的开头语句中有所介绍：

允许将显式值插入到表的标识列中。

（强调我的）

推荐阅读