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

edit_mappings

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

enable_identity_insert

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

   

About Arsalan Khan

People know me as 'Khan' ... nope not Shah Rukh's movie but Star Trek II - Wrath of Khan and since than 'Khan' it has been. Born and raised in Dubai before moving to US for my masters. Been blessed with solid 8+ yrs with SQL (and counting) and currently working for the biggest publishing house as Sr. Database Administrator. I have had an opportunity to speak about tips & tricks to write efficient tsql for Quest International Users Group (PeopleSoft). Love playing TT, badminton and thoroughly enjoy watching cricket when not occupied with my loving daughter. Expertise in DR, Performance Tuning, Troubleshooting and Problem Solving. With that being said I have finally decided to roll my sleeves up and give back to the community bit by bit. Finally.. as my wonderful wife puts it ... "if you don't have a smile, I will give you one of mine ~ Rabia Khan" ~Cheers

View all posts by Arsalan Khan →

3 Comments on “Failure Inserting into the Read-Only Column”

  1. Hi dear Khan,

    Could you please help me in inserting into a date field which is read only and calculated column ,I want to insert the data from another table and I want to load that column to my destination whee as its calculated column (getdate ())

    1. Thanks. I’m no SQL Server expert and I was sweating bullets over this one. Even though I was messing with a development instance I was still stuck. Thanks for taking the time to post this resolution. Worked like a charm.

Leave a Reply

Your email address will not be published.