SQL Bulkcopy for Client / Parent relationship

Question

Hi,

we have 2 DataTables in a .NET application having a Client / Parent relationship with millions of rows. This data should be inserted into a SQL Server database via SQL BulkCopy. It is possible that multiple instances of this .NET application run in parallel inserting different data to the same tables.

For the automatic generation of the primary keys We have an identity column in the parent table.

The problem is we don't know how to insert the corresponding foreign keys into the child table.

Does anybody know how to solve this 1:N relationship problem using bulkcopy? We cannot create the IDs in .NET because multiple instances might run.

Thanks Daniel

Answer 1

This is just one of many reasons why I prefer using natural keys over IDENTITY for all of my primary keys.

How do you know which children go to which parent in the data source? If they are linked by a column there then it shouldn't be too difficult. Insert the parent rows, insert the children rows into a staging table, then insert the children rows while joining to the parents on that column to get the ID.

If the relationship is simply by the relative positions in a file or something similar to that then it becomes more difficult. You can either include the file line # and then use the same steps above, joining on those (child.line_num > parent.line_num and no other parent.line_num is between those two numbers) or you can import the parents one at a time in a loop, capture the ID, then insert all of the its children using that ID. You can set this up in SSIS without too much trouble, but it can be slow.

With more details on your problem (data source, etc.) I might be able to provide more specifics.

Answer 2

Not sure where your data is, but this is how I would approach this.

1. Use intermediate flat files, one or more.
2. Flatten your data and assign a primary key.
3. Enable identity insert for the parent table (means skip auto-increment)
4. Use SSIS to load your database, instead of just bulk copy.