0

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Eduardo Mello
  • 925
  • 3
  • 15
  • 32

3 Answers3

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.

richardtallent
  • 34,724
  • 14
  • 83
  • 123
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.

Cade Roux
  • 88,164
  • 40
  • 182
  • 265
  • I believe the user is talking about keeping the designation of the primary key... there is a check box allowing you to to keep primary key values in whatever field is the primary key, but they are not automatically marked. I'm not sure how better to explain it, but this has been a big road block for me. – one.beat.consumer Sep 04 '15 at 23:29
0

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.

masoud ramezani
  • 22,228
  • 29
  • 98
  • 151