SQL Identity Insert Error

The below error appears when SQL Server blocks an insert because your integration is trying to set a value for a column that is marked as IDENTITY (auto-increment). By default, SQL generates values for these columns and does not allow you to provide them unless you explicitly enable IDENTITY_INSERT.

Cannot insert explicit value for identity column in table 'XYZ' when IDENTITY_INSERT is set to OFF.

The steps below explain what the error means, why it happens, and how to fix it safely.

1. Option A – Do not insert into the identity column (Recommended in most cases)

• Remove that column from the insert payload.
• SQL Server will assign the value automatically.

2. Option B – Advanced (if you must control the ID)

• Temporarily allow manual inserts by enabling IDENTITY_INSERT.

Manual SQL

SET IDENTITY_INSERT dbo.MyTable ON;
INSERT INTO dbo.MyTable (IdentityCol, ColA, ColB)
VALUES (123, 'Value1', 'Value2');
SET IDENTITY_INSERT dbo.MyTable OFF;


3. Option C - Rapidi (integration scenario)

• Use Destination Pre Execution ( Go to the transfer that needs this setup and expand the Triggers section -> populate the field Destination Pre Execution with the below information: 

SET IDENTITY_INSERT [dbo].[%%COMPANYNAME%$Price List Line$<GUID>] OFF

Click on Activate Changes and run again the transfer. All records should be transferred without any issues.