Bulk insert with child records

Hi,

Maybe this is a really silly problem, but I was wondering if anyone could help me find a solution.

On a daily basis I have to insert about a 1000 new (parent) records in table, with about 25000 corresponding (child) records.

The two tables a related through a parent-child relation (obviously). The primary keys in both tables are identities. The foreign key in the child table is linked to the primary key in the parent table.

I would like to use a bulk insert statement to insert the records for both tables, but the problem is that the foreign key in the child table is not automatically updated to the new value of the primary key in the parent table.
Does anyone know a work-around?? Maybe there's a better way of inserting the records?Originally posted by ChrisHens
The two tables a related through a parent-child relation (obviously). The primary keys in both tables are identities. The foreign key in the child table is linked to the primary key in the parent table.


If I understand your problem right, you want to translate an existing foreign-key-relation into a new one, based on identities?

You need to insert your parent records first, but you will have to keep your old key in your parent table. During insert of your child records, join with the parent table on your old key, and insert the newly created key as foreign key into the child table.|||Originally posted by DoktorBlue
If I understand your problem right, you want to translate an existing foreign-key-relation into a new one, based on identities?

You need to insert your parent records first, but you will have to keep your old key in your parent table. During insert of your child records, join with the parent table on your old key, and insert the newly created key as foreign key into the child table.

Yes that's exactly what I am trying to achieve. But if I am to keep my old key in the parent table, wouldn't I need an extra field? Wouldn't this also imply that after the insert of the parent and child records that I will have to somehow set the old parent key to a default value? Otherwise future joins might fail right?|||You are correct in all your points:
1) you need the old ID as an extra field, but this isn't really a problem. Often, the old ID is even functional to allow te refer to your source system.
2) if you expect to get the same parent ID in the future again, you can "flush" your old IDs after processing, or you can add some batch processing control. Options are to add a batch number (as an extra field or as unifying part of your old ID), or you can consider to make a batch control table, storing the last new ID (assuming that the identity is continue increasing) of your previous batch.

Options enough, it's depending on your situation; I'm curious what you will do.