Identity Insert and SQL Server 2005 Import Export Wizard


Have you ever run the SQL 2005 Server Import Export Wizard and noticed the identity values for the destination table are renumbered?  The SQL 2005 Server Import Export Wizard setting “Enable identity insert” is ignored when selecting “Optimize for Many Tables” causing the identity values at the destination to be renumbered.


The “Optimize for Many Tables” creates a SSIS package transferring the tables one-at-a-time within a For…Each looping structure.  It does not include the logic to enable identity insert.  Not selecting “Optimize for Many Tables” creates a SSIS package which transfers tables in parallel.


If you have many tables to transfer and you are concerned about server performance, don’t select “Optimize for Many Tables” but instead set the Package property MaxConcurrentExecutables.  You may need a couple of attempts to find the balance between performance and server load.


Hope you found this helpful.


All posting are provided “AS IS” with no warranties, and confers no rights.


Comments (8)

  1. mroku says:

    Hi!

    I’m afraid your workaround doesn’t work. When you try to copy data from multiple tables joined by foreign keys you will get missing key errors if you don’t use "Optimize for many tables" option.

    My impression over SSIS is very very poor. So far everything I try to do with it doesn’t work at all or doesn’t work as expected.

  2. da9l says:

    I’ve encountered this problem as well and I’d really like to get it fixed.

    To make microsoft fix this, please vote for feedback item 135905 at connect.microsoft.com under the SQL server feedback connection!

    /Daniel

  3. zlusc says:

    I don’t think the "Optimize for many tables" causing that trouble. I tried many times even only import one table, the default setting still keep Enable identity insert unchecked. It is really annoying.

  4. e6matt says:

    I am exporting from SQL 2005 to SQL 2000. I am only doing one table with “Optimize for many tables” unchecked. “Enable Identity Insert” is also checked for the single table I am copying. The preview correctly shows the Primary Key column jumping from 71 to 73 (I deleted a row). However, after running the export the SQL 2000 table has an entry for 71 and 72. It doesn’t matter what is checked, this always happens. Is there another way around?

  5. bruce.parker says:

    I needed to copy a complete database from 2005 to 2000

    This is how I did it – Assuming the table(s) you need in 2000 are all created with indexes, constraints etc but contain no data.

    Run the following on the 2000 database

    exec sp_msforeachtable ‘ALTER TABLE ? NOCHECK CONSTRAINT ALL’

    exec sp_msforeachtable ‘ALTER TABLE ? DISABLE TRIGGER ALL’

    Then use the export wizard on 2005,

    select all tables and views

    Edit Mappings and check the Enable Identity Insert box

    UN check the Optimise for many tables box

    Deselect ALL the views so that it is only tables that are copied

    Run the export ignoring the warning

    After the export is complete

    Run the following on the 2000 database

    exec sp_msforeachtable ‘ALTER TABLE ? CHECK CONSTRAINT ALL’

    exec sp_msforeachtable ‘ALTER TABLE ? ENABLE TRIGGER ALL’

    This has ceratinly worked on the table I was having trouble with – I still need to check the rest but it is looking promising

    I hope this helps

  6. fcsobel says:

    After spending a day fighting with this I learned about SqlBulkCopy from a post on David Hayden’s blog and created this quick app to get around the problem.

    It can copy data and keep identity and nulls intact. Used with the generate script wizard you can make a complete database copy as long as you have sql access.

    It’s like having the SQL 2000 wizard back:)

    You can get it here:

    http://projects.c3o.com/files/3/plugins/entry11.aspx

  7. Anonymous says:

    Having fought with the identity crisis in SQL2005 I found a neat way around it – Use a SQL2000 installation on another server on the network to do a cross import into the SQL2005 tables. It actually works. Alternatively modify all identity columns by changing them into normal int columns, do the import  and re-instate the identity columns after the import.