How do I bulk insert two datatables that have an Identity relationship
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?
No comments:
Post a Comment