Copy data from SQL Server Express keep primary key

Question

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

Answer 1

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.

Answer 2

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.

Answer 3

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.