Sql-server – foreign key relationships and auto-incrementing in Sql Server

importsql server

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?

Best Answer

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).

Related Question