Export Data and maintain Identity and Primary Key

  • SQL Server 2005 Export from a development server to a production server using the "SQL Server Import and Export Wizard" and "Table Copy" seems to copy everything (data and structure), but can't maintain any info for identity and primary key. I can use the "Edit Mappings" to manually type in the identity, but what an unnecessary pain when you have a large database with 100s of tables. Am I missing a setting somewhere in the wizard to maintain my identities and primary keys? Would have thought the default action would do this, but apparently not.

    Any help is greatly appreciated!!!

  • Generate script to create these tables. It would take care of the indexes, constraints, etc.

    Then use the export wizard to export only the data.

  • YOu can use a "Select Into" to maintain your identity in the new table, but it won't retain PK / Relationship stuff. Suresh is correct. The best way to handle retaining that information is to script out (then rename) the table, and then do an insert of the data.

    The second best way is to script out the PK, do a Select Into to your new table, then right after the SI, run the PK script (making sure it's pointing to the new table).

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Thanks for the input. I was affraid that was my only option. It's not a bad one, just an extra step and different from the old DTS of a full DB.

  • I know this is a very old post, but came across this after realising using Native SQL Export/Import methods that i also had this issue not having the Identities being set and kept when transferring data. Although - i then found and wanted to share BACPAC export / import files do keep everything intact and worked for me ok.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply