Copy data from SQL Server Express keep primary key

I want to copy data from a database to another, but I'd like to keep the primary key from the origi开发者_StackOverflownal database so it won't mess with the relationships. I'm using SQL Server 2008.

As already mentioned, IDENTITY_INSERT is the flag you'll want to set for each table to allow you to set a particular value on the identity column for each inserted record.

But if you're merely trying to duplicate an entire existing database (or most of it), not just copy a few tables, I recommend just doing a full backup of the database, restore the BAK file to a new database name, and then make your modifications to the clone.

I assume your primary keys are IDENTITY? If so, you should use SET IDENTITY_INSERT ON for the table (only one table at a time) - if you use a tool like SSIS or similar for the transfer, there is usually an option to allow you to insert identities.

you can use bcp command for this work. with bcp command you can bulk copy your data. this command have a switch that keep your identity.

for see more details go to this link.