foreign key relationships and auto-incrementing in Sql Server I have two instances of a database with these two tables:


Foo


foo_id &lt;-- primary key


Bar


bar_id &lt;-- primary key

foo_id &lt;-- 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?
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).