Menu
I want to move data from one DB to another, while keeping the identity column values in the old DB. When I run the wizard I get a validation error "Failure inserting into the read-only column "ChannelID".
Where ChannelID is an identity column on that table. How can I override the identity functionality in order to be able to import the data?
450
Enabling the property “Enable Identity Insert” by checking the checkbox allows the values to be inserted in the identity field. This way, the exact identity values are moved from source database to the destination table.
The Import and Export wizard in SQL Server Management Studio (SSMS) assists users with copying data from one location to another. The export tasks lets you export data into another database, Excel, text files, etc. and the import tasks let you load data from other databases or sources like Excel, text files, etc.
After you select the existing tables and views to copy or review the query that you provided, if you click Edit mappings, the SQL Server Import and Export Wizard shows the Column Mappings dialog box. On this page you specify and configure destination columns to receive the data copied from the source columns.
You'll need to Enable identity insert. There's a check box when you click the Edit Mappings.
138
Byte56's answer is correct - check "Enable identity insert" from the Edit Mappings screen. I just wanted to point out that the "Select Source Tables and Views" list supports multi-select, so you can hold down Shift or Ctrl to select multiple tables in the list.
42
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
