Insert and set the Identity value using SET IDENTITY_INSERT

Insert and set the Identity value using SET IDENTITY_INSERT

Here we look at how to insert records and set the ID value, on a table which creates ID values automatically.

We need to use the SET IDENTITY_INSERT command, setting it to ON, make the insert, then switch it to OFF.

Here is an example where we are inserting a record with a specific value, we are actually selecting a record from the same table and then inserting a copy of the values as a new record, but with a new ID of -1:

SET IDENTITY_INSERT [Sections] ON

insert into [dbo].[Sections] (id,
[SectionTitle]
,[FileName]
,[TimeStamp])
SELECT -1
,[SectionTitle]
,[FileName]
,[TimeStamp]
FROM [dbo].[Sections]
where id = 12

SET IDENTITY_INSERT [Sections] OFF