SQL Server Data Import – How To Preserve Primary Keys and Identity Fields

One of my biggest problems working with SQL Server databases is preserving my primary keys and identity fields when I try to copy tables from one database to another. The SQL Server Import and Export Wizard likes to remove primary keys and you lose your identity fields. This results in many errors when the web application attempts to insert new records without providing a value for the identity field because it is suppose to auto-increment.Today I figured out how to work around this problem using SQL Server 2005. Needless to say, I did not find much information about this topic on the Internet. The first thing you should do is run the Script Wizard to generate CREATE statements. Make sure the Script Primary Keys option is set to True. The tables in the destination database need to be created by running this script in order to get your primary keys and identity fields. The tables will not contain any rows of data.

Next you run the SQL Server Import and Export Wizard to copy your data into the tables. The trick here is to click the Edit Mappings button in the SQL Server Import and Export Wizard and check both Delete rows in existing destination tables and Enable identity insert in Transfer Settings.

SQL Server Import and Export Wizard

Transfer Settings

Unfortunately, this won’t help you if you want to import data into an existing table. You can’t just delete all its rows and import using identity insert or you’ll wind up with ID numbers that increment from the last value of the identity field. You need to drop the table and recreate it from a script to preserve your exact identity field ID numbers.

This entry was posted in Databases. Bookmark the permalink.

8 Responses to SQL Server Data Import – How To Preserve Primary Keys and Identity Fields

  1. Gary says:

    Either I did this wrong or it doesn’t achieve my objectives. Issue is join tables that maintain values from the source database. Over time as rows are deleted from the source database the numbers will no longer be sequential. But when I stepped through the routine you described the identity column in the destination database was filled with sequential values starting from 1. This of course breaks all the relationships maintained in the join tables.

    [Reply]

  2. Mike says:

    Which is the solution when the table names are different? (trying to import data to my current DB from an external one)

    Thanks

    [Reply]

  3. Mark says:

    Thank you for this post — I was pulling out my hair trying to get around this!

    [Reply]

  4. michael says:

    those people at microsoft should figure this, what do user need to do, and what action is necessary.

    they should release microsoft sqlserver with better functionality

    [Reply]

  5. Jeff says:

    Thanks, this helped me a lot!

    [Reply]

  6. abdiel says:

    in script wizard you can select option to script “Schema and Data”, that would solve Gary’s problem

    [Reply]

  7. Muthu says:

    Thanks. Very good info.

    [Reply]

  8. potFlix says:

    Thanks MAN, I have been struggling for days on doing that. GREAT POST

    [Reply]

Leave a Reply

Your email address will not be published. Required fields are marked *