数据迁移，但保持关系完整 [英] Data Migrations but keeping the relationships intact

问题描述

大家好

我有两个桌子

表1
____________________________
ID |第I栏|第II栏|
----------------------------

表2
____________________________
ID |表I ID | X列|
----------------------------

ID是每个表中的主键，并设置为自动递增.我需要做的是从结构完全相同的另一个数据库表中导入数据，而又不会破坏它们之间的关系.

我尝试使用批量复制，但是ID更改了，并且关系船不成立.

一种解决方案是将数据导入数据集中，然后遍历每一行，然后在另一数据集中插入新行，并保留一个存储新ID和旧ID的字典.但它的效率不高，因为我处理的行数远远超过100,000.

我忘了提一下，我必须使用C#v3.5来做到这一点

任何指针??

预先感谢

Hi All

I have two tables

Table1
____________________________
ID | columns I |column II |
----------------------------

Table 2
____________________________
ID | Table I ID | column X |
----------------------------

IDs are the primary key in each table and are set to auto increment. What I need to do is to import data from another DB''s tables with exactly the same structure without breaking the relationships.

I have tried to use bulk copy but the ID get changed and the relation ship doesn''t hold.

One solution is to import the data in the dataset and then iterate through each row and insert a new row in another dataset and keep a dictionary storing the new and old IDs. But its not efficient as I am dealing with row counts going well above 100,000.

I forgot to mention that I have to do this using C# v3.5

Any pointers ??

Thanx in advance

推荐答案

将记录从源表一插入到目标表一
使用select @@IDENTITY确定插入到目标表one中的记录的ID

将源表2中的相关记录插入到目标表2中，并将它们与@@IDENTITY

Insert a record from source table one to destination table one
use select @@IDENTITY to determine the ID of the record inserted in destination table one

insert related records from source table two to destination table two, and related them to the result of the @@IDENTITY

的结果相关联.将标识列设置为off，然后插入数据.插入数据后，可以将idedity设置为on.

Set the identity column off and then insert the data. Once the data is inserted, you can set the iedntity as on.

这是对我有用的解决方案，即在创建"SqlBulkCopy"实例时，请使用"SqlBulkCopyOptions.KeepIdentity"和复制两个表.这样，所有关系都将保持完整.

Here is the solution that worked for me and that is while creating the "SqlBulkCopy" instance use "SqlBulkCopyOptions.KeepIdentity" and copy both the tables. In this way all the relations will be intact.

//connString is your connection string
SqlBulkCopy bulkCopy = new SqlBukCopy(connString, SqlBulkCopyOptions.KeepIdentity);

问候

Regards

这篇关于数据迁移，但保持关系完整的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！