Eytch Eytch - 5 months ago 11
SQL Question

INSERT record to SQL table with IDENTITY column

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

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