Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2003
    Posts
    2

    Arrow Unanswered: 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?

  2. #2
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447

    Re: Bulk insert with child 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.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  3. #3
    Join Date
    Sep 2003
    Posts
    2

    Re: Bulk insert with child records

    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?

  4. #4
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447

    Re: Bulk insert with child records

    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.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •