Failure inserting into the read-only column

At times when transferring data from one database to other using either Import Export wizard or using SQL Server Integration Services, you may encounter the following error:

Failure inserting into the read-only column .(SQL Server Import and Export Wizard)

This happens when the source table has an identity column which is auto-generated at source and also at the destination. Since it’s auto-generated by system, import/export can’t overwrite identity data and treats it as read-only column. The best way to resolve this issue is to enable IDENTITY INSERT before you Import/Export data from tables.

--Execute this on the destination table BEFORE executing Import/Export.
 
SET IDENTITY_INSERT <TABLE_NAME> ON;

Once the data is imported/exported, you may turn disable IDENTITY INSERT using the below statement

--Execute this on the destination table AFTER executing Import/Export.
 
SET IDENTITY_INSERT <TABLE_NAME> OFF;

Alternatively, you can also fix this by enabling identity insert from the IMPORT/EXPORT GUI as shown below:

From the IMPORT/EXPORT wizard, select the table and click on Edit Mappings as shown in the below screen

You will now be presented with the column mappings dialog box where you can click Enable Identity Insert checkbox.

Click on OK to close the window and re-run the process and you should now be able to copy identity data in the destination object.