Export Data and maintain Identity and Primary Key

Question

Post reply

Export Data and maintain Identity and Primary Key

brett-925381

SSC Veteran

Points: 204
More actions
October 29, 2008 at 5:06 pm

#193049

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!!!

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

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

Suresh B. SSC-Insane Points: 22986 More actions · Answer 1

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.

Brandie Tarvin SSC Guru Points: 173002 More actions · Answer 2

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.

brett-925381 SSC Veteran Points: 204 More actions · Answer 3

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.

zerokewl88 Newbie Points: 1 More actions · Answer 4

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.