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.

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.