Failure Inserting into the Read-Only Column

A common snag that I have seen while using the Import & Export wizard over the years is running into – Failure Inserting into the Read-Only Column. Lot of us have panicked but the reality is to simply click on the check box OR write a 2 line tsql code. Here is what the error looks like when inserting into an Identity column:

Error 0xc0202049: Data Flow Task 1: Failure inserting into the read-only column “ID”.(SQL Server Import and Export Wizard)

I will be presenting 2 quick methods

Using the Wizard

• In the Import Export Wizard, after selecting the table(s) for copy, click on Edit Mappings

• In here click on Enable identity insert — this will let you copy your identity columns

Using TSQL

• Before starting the Import Export Wizard you can type this

SET IDENTITY_INSERT [MYTABLE_NAME] ON

-- start Import Export Wizard process and once data is loaded correctly
-- execute the following

SET IDENTITY_INSERT [MYTABLE_NAME] OFF

~ Adios

Khan

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook |

Follow me on Twitter