如何批量插入具有身份关系的两个数据表 [英] How do I bulk insert two datatables that have an Identity relationship

问题描述

• 创建新的Employee datatable


我使用SQLBulkCopy， li> make new EmployeeAddress datatable

• 填充employee表，但不要具体描述employeeId，因为它的身份


• 填充EmployeeAddress datatable，但它包含一个employeeId字段


• 将两个数据表两次写入数据库，并更改表名：

  using（var bulk = new SqlBulkCopy（connectionString，SqlBulkCopyOptions.KeepIdentity& SqlBulkCopyOptions.KeepNulls））
 { 
 bulk.BatchSize = 25; 
 bulk.DestinationTableName =Employee; 
 bulk.WriteToServer（employeeDataTable）; 
} 
  

如何在第二个datable中指定employeeId，使其正确对齐第一次插入的员工？我读取数据的方式是我一起阅读员工和地址，所以插入所有的员工然后回来并插入所有的地址是有点痛苦。

我想知道是否有优雅的解决方案？

解决方案

我不太确定这是多么优雅，我想这是由你决定的。我假设你有一个独特的字段或在员工数据中唯一的字段组合。

如果在服务器上创建一个暂存表，您可以批量插入具有所有员工数据以及员工地址信息的数据。

从登台表中插入员工数据。然后从登台表中加入employee表（获取新分配的id）并插入员工地址数据。

最后删除暂存表。

所有内容仍然面向设计，所以你应该有很好的表现。 / p>

I'm using SQLBulkCopy, in pseudocode I do this:

• make new Employee datatable
• make new EmployeeAddress datatable
• populate employee table but don't specificy employeeId as it's identity
• populate EmployeeAddress datatable, but it contains an employeeId field
• write both datatables to the database by doing this twice and changing the table name:

.

using (var bulk = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.KeepIdentity & SqlBulkCopyOptions.KeepNulls))
    {
        bulk.BatchSize = 25;
        bulk.DestinationTableName = "Employee";
        bulk.WriteToServer(employeeDataTable);
    }       

How can I specify the employeeId in the 2nd datable so that it aligns correctly to the employee that was inserted the first time? The way I read the data is that I read the employee and the address together, so inserting all of the employees then going back and inserting all of the addresses is a bit of a pain.

I was wondering if there was an elegant solution?

解决方案

I'm not too sure how elegant this is, I guess that is up for you to decide. I am assuming you have a unique field or combination of fields that are unique in the employee data.

If you create a staging table on the server, you can bulk insert the data that has all of the employee data as well as the employee address information.

From the staging table, insert the employee data. Then from the staging table join the employee table (to get the newly assigned ids) and insert the employee address data.

Finally drop the staging table.

Everything is still set oriented, so you should have good performance.

这篇关于如何批量插入具有身份关系的两个数据表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！