1

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?

NibblyPig
  • 51,118
  • 72
  • 200
  • 356
  • Are you using the auto identity of the table? – ScottGuymer Aug 29 '13 at 14:30
  • Yes, so the employeeId is auto generated by SQL and I want to create an employee and an address, but I don't know the employeeid until the employee is created in the database and SQL assigns it one - but to create an address, I need the employeeid it belongs to. – NibblyPig Aug 29 '13 at 16:24

1 Answers1

2

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.

Gary Walker
  • 8,831
  • 3
  • 19
  • 41
  • Im assuming that there could be multiple addresses for each employee? you could turn the identity insert off and insert your own id's generated consecutively on the code side (is this a one off thing or part of the production running of the system?) – ScottGuymer Aug 29 '13 at 16:36
  • I think I could possibly do that, it's early stages of how big the project will be but that is also something to consider. Thanks. – NibblyPig Aug 29 '13 at 16:37
  • If you have 3 addresses, you can denormalize them in your staging table and then run 3 commands for to insert the address info. – Gary Walker Aug 29 '13 at 18:18