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