INSERT record to SQL table with IDENTITY column

Question

i have this sql table

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)..

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?

Answer 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

Answer 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.