Import and Export Wizard with Identity Column

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?

You'll need to Enable identity insert. There's a check box when you click the Edit Mappings.

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.

In EDIT COLUMN MAPPINGS, Tick ENABLE IDENTITY INSERT. Then click EDIT SQL button.

Change

[MyID] int NOT NULL,

into

[MyID] int IDENTITY(1,1),

Works in importing MS Access database. This preserves the data in Autonumber field being converted to Identity column.

You should use SET IDENTITY INSERT ON to allow the insert of values in an IDENTITY column

Here is a link to MSDN

SQL Server 2008 R2 using SSMS 2008 R2.

Simply enabling identity on the "Edit Mappings" does not retain identity=true. I have to manually edit the sql for each table and include the IDENTITY property in the sql code.

Example: ADD MS_IDENT numeric(18,0) IDENTITY NOT NULL,

I just upgraded to SQL Server Management Studio 2017. I tried checking the "Use identity" checkbox in the Edit Mappings dialog but this did not work. When updating an existing table, the wizard doesn't let you edit the SQL. Instead, I was able to save it as a package on my hard drive. I then edited the file in Notepad, searching for "identity". Sure enough, the property was set to true, which according to the comments within the file itself, means the value for the identity column is copied from the source. Changing it to false results in the value being auto generated in the destination. Hope this helps.

I have spent some time trying to find a solution for this

I have an access database with 300+ tables and using SSMS2017 import tool I could not get it to preserve the primary key and auto number, without manually editing the config for each table.... not good..

In the end I used a tool made by bullzip, (MS Access to MSSQL) which allows me to convert from access to mssql (they also have a tool for access to MySQL)

the tool does the job for you, wither to a script if you have a small table, or directly to the server..

All ID's are intact and autonumbers are also in there..

job done.