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 |
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 ())
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.
thank you for this suggestion that worked for me.