将记录插入到带有 IDENTITY 列的 SQL 表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16766963/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
INSERT record to SQL table with IDENTITY column
提问by Eytch
i have this sql table
我有这个 sql 表
CREATE TABLE Notes(
NoteID [int] IDENTITY(1,1) NOT NULL,
NoteTitle [nvarchar](255) NULL,
NoteDescription [nvarchar](4000) NULL
) CONSTRAINT [PK_Notes] PRIMARY KEY CLUSTERED
(
NoteID ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
And i want to copy records from a temporary table INCLUDING the NoteID(using sql query)..
我想从一个临时表中复制记录，包括 NoteID（使用 sql 查询）。
this is my script:
这是我的脚本：
SET IDENTITY_INSERT Notes OFF
INSERT INTO Notes (NoteID, NoteTitle,NoteDescription)
SELECT NoteID, NoteTitle,NoteDescription from Notes_Temp
SET IDENTITY_INSERT Notes ON
with this script, i'm getting an error:
使用这个脚本，我收到一个错误：
Cannot insert explicit value for identity column in table 'Notes' when IDENTITY_INSERT is set to OFF.
is there other way of insert records to a table with identity column using sql query?
有没有其他方法可以使用 sql 查询将记录插入到带有标识列的表中？
回答by Adriaan Stander
Change the OFF and ON around
改变 OFF 和 ON 周围
SET IDENTITY_INSERT Notes ON
INSERT INTO Notes (NoteID, NoteTitle,NoteDescription)
SELECT NoteID, NoteTitle,NoteDescription from Notes_Temp
SET IDENTITY_INSERT Notes OFF
回答by Ahmed
SET IDENTITY_INSERT Notes ON
INSERT INTO Notes
/*Note the column list is REQUIRED here, not optional*/
(NoteID, NoteTitle,NoteDescription)
SELECT NoteID, NoteTitle,NoteDescription from Notes_Temp
SET IDENTITY_INSERT Notes OFF
You're inserting values for NoteId that is an identity column. You can turn on identity insert on the table like this so that you can specify your own identity values.
您正在为作为标识列的 NoteId 插入值。您可以像这样在表上打开身份插入，以便您可以指定自己的身份值。
回答by Old Pro
Presumably you are using SQL Server (you don't say) and have misunderstood the meaning and purpose of IDENTITY_INSERT. In general, you are not allowed to explicitly set the value of an IDENTITY column, but by setting IDENTITY_INSERT to ON for a table you can temporarily permit such inserts.
想必您正在使用 SQL Server（您没有说）并且误解了IDENTITY_INSERT的含义和目的。通常，不允许显式设置 IDENTITY 列的值，但通过将表的 IDENTITY_INSERT 设置为 ON，您可以暂时允许此类插入。
