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 “ID”.(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.

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

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

sqlimportexport1_sqlcommunity_com

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

sqlimportexport2_sqlcommunity_com

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.