The SET IDENTITY_INSERT statement in SQL Server allows explicit values to be inserted into the identity column of a table. This can be useful when you need to insert specific values into a column that is normally auto-incremented. Here is a brief overview of how it works:

Syntax

SET IDENTITY_INSERT [database_name].[schema_name].[table_name] { ON | OFF }

Usage

• ON: Allows explicit values to be inserted into the identity column.
• OFF: Resumes the normal auto-increment behaviour.

Example

USE [Liberate_Emails]
GO
SET IDENTITY_INSERT [dbo].[Files] ON
INSERT INTO [dbo].[Files] ([ID], [CaseCode], [CreateDate], [ModifiedDate], [FileName], [FileData], [FileType])
SELECT [ID], [CaseCode], [CreateDate], [ModifiedDate], [FileName], [FileData], [FileType]
FROM [SQLDBA].[dbo].[results]
GO
SET IDENTITY_INSERT [dbo].[Files] OFF

This command is particularly useful when migrating data or restoring databases where the original identity values must be kept.

From my SQL Server DBA handbook.