1

I have two instances of a database with these two tables:

  1. Foo

foo_id <-- primary key

  1. Bar

bar_id <-- primary key

foo_id <-- foreign key


bar_id and foo_id are automatically incremented, read only primary keys.

What I want to do is this:

  • On database instance 1, I drop all the rows in foo and bar
  • Import the rows from database 2 to database 1

How can I keep the relationship between tables Foo and Bar in this setup? That is, whatever the primary key in Foo is, the corresponding row in Bar has the right foreign key value to Foo no matter what value foo_id has?

2

You can issue the command

SET IDENTITY_INSERT tablename ON

before inserting values into a table and call

SET IDENTITY_INSERT tablename OFF

afterwards.

According to the documentation for SET IDENTITY_INSERT (Transact-SQL):

At any time, only one table in a session can have the IDENTITY_INSERT property set to ON.

and

If the value inserted is larger than the current identity value for the table, SQL Server automatically uses the new inserted value as the current identity value.

To keep the foreign key relationship simply import the main table (Foo) before the details table (Bar).

0

You will need to truncate tables foo and bar on db1 so the identity seed is reset

You will need to "set identity insert on" on foo and bar on db1

Load tables foo and bar on db1 from tables foo and bar on db2.

Turn identity insert off

IDENTITY INSERT

You may need to reseed the next identity value in foo and bar since you loaded rows from db2

DBCC CHECKIDENT

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.