使用外键引用身份列将数据迁移到Azure [英] Data migration to Azure with foreign key referencing an identity column

问题描述

我希望将本地数据库迁移到Azure.但是，我的本地数据库的表Table1的列为

I wish to migrate my on-premise database to azure. However, my on-premise DB has a table Table1 with column as

ID int identity(1,1) Not Null,

和表'Table2'中的列'column1'在表Table1.ID上具有外键约束

and a column 'column1' in table 'Table2' has a foreign key constraint on column Table1.ID

当我的Table1.ID列没有连续值时，我遇到了一个问题.

I am facing a problem when my Table1.ID column does not have consecutive values.

例如，Table1.ID具有值(1、2、4、5、6 ...)

For example, Table1.ID has values (1, 2, 4, 5, 6...)

如果我正常插入Azure表，则Azure上的Table1.ID将是(1、2、3、4、5 ...)并弄乱了我的外键关系.我想防止外键关系的混乱.

If I insert normally into the Azure table, my Table1.ID on azure will be (1, 2, 3, 4, 5...) and jumble up my foreign key relationships. I want to prevent this jumbling up of foreign key relationships.

关于如何执行此操作的任何想法?

Any ideas on how to do this?

推荐答案

在执行插入操作之前，请对该表启用身份插入:

Before doing your inserts, enable identity-inserting for that table:

SET IDENTITY_INSERT Table1 ON

然后，您可以在插入内容中包含ID列，以使ID值保持不变.稍后，当您插入具有Table2的FK引用的行时，ID值将相同.

And then you can include the ID column in your inserts, so that the ID values stay the same. Later, when you insert the rows that have FK references for Table2, the ID values will be the same.

只需确保完成后关闭身份插入功能即可

Just make sure when you are done to turn identity-insert back off when you are done:

SET IDENTITY_INSERT Table1 OFF

这篇关于使用外键引用身份列将数据迁移到Azure的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！