将记录插入带有标识列的SQL表

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查询向具有标识列的表插入记录还有其他方法吗?

3 个解决方案

#1

Change the OFF and ON around

把开关打开

SET IDENTITY_INSERT Notes ON

INSERT INTO Notes (NoteID, NoteTitle,NoteDescription)
SELECT NoteID, NoteTitle,NoteDescription from Notes_Temp

SET IDENTITY_INSERT Notes OFF

#2

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插入值。可以像这样打开表上的标识插入，以便指定自己的标识值。

#3

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_INSERT，可以暂时允许此类插入。

#1